Results 1 to 16 of 16
  1. #1
    Join Date
    May 2005
    Location
    United Kingdom / England
    Posts
    557

    MySQL table dosent accept comma's?

    Hi i have a php page and all articles enter fine.

    But when i tried entering a line which had a comma in it "'" it fails to enter.

    I cant see what i am doing or did when i first created the table.

    This is the table:

    CREATE TABLE `article` (
    id int(4) NOT NULL auto_increment,
    firstname varchar(30) NOT NULL default '',
    surname VARCHAR(30) NOT NULL default '',
    locality VARCHAR(30) NOT NULL default '',
    username varchar(65) NOT NULL default '',
    title varchar(65) NOT NULL default '',
    article varchar(1000) NOT NULL default '',
    submission_date varchar(60) NOT NULL default '',
    PRIMARY KEY (`id`)
    ) TYPE=MyISAM AUTO_INCREMENT=2 CHARSET=latin1;


    I believe it has something to do with the charset? Well ive changed that to utf8 also but still no hope.

    Any ideas? Thanks!

    Cheers
    Kayz

  2. #2
    Join Date
    Feb 2003
    Location
    Canada
    Posts
    958
    Are you referring to a comma (, or chr(44)) or a single quote (' or chr(39))?

    If you are referring to a single quote, then you are probably not escaping it properly and the insert statement is interpreting it as the end of a value

  3. #3
    Join Date
    May 2005
    Location
    United Kingdom / England
    Posts
    557
    Quote Originally Posted by Adam-AEC View Post
    Are you referring to a comma (, or chr(44)) or a single quote (' or chr(39))?

    If you are referring to a single quote, then you are probably not escaping it properly and the insert statement is interpreting it as the end of a value
    Sorry im talking about this apostrophe: ' for example can't.
    Kayz

  4. #4
    Join Date
    Feb 2005
    Location
    Poland
    Posts
    248
    that query IS working here im mysql 5.0.x
    http://www.linkedin.com/in/marcinlulek - my linkedIn profile
    webdeveloper for hire - XHTML, CSS3 PYTHON ,PostgreSQL, Ajax & Javascript, I build apps with Pyramid/Pylons Web frameworks using dojo toolkit for js work

  5. #5
    Join Date
    Feb 2003
    Location
    Canada
    Posts
    958
    Are you escaping the single quotes when you perform your query?

    ie. mysql_real_escape_string

  6. #6
    Join Date
    May 2005
    Location
    United Kingdom / England
    Posts
    557
    Quote Originally Posted by ergo View Post
    that query IS working here im mysql 5.0.x
    Yes the query will work, it executes fine. But when it comes to inserting the data via a form it gives me this error:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near.. (letter "x" which has an apostrophe.)
    Kayz

  7. #7
    Join Date
    May 2005
    Location
    United Kingdom / England
    Posts
    557
    Quote Originally Posted by Adam-AEC View Post
    Are you escaping the single quotes when you perform your query?

    ie. mysql_real_escape_string
    No i dont think so, how do i apply this and where do i apply it?

    Cheers.
    Kayz

  8. #8
    Join Date
    Feb 2003
    Location
    Canada
    Posts
    958
    You need to find where your script is performing the INSERT query, and have it escape the query before it sends to the database.

    Look for a SQL line that begins with INSERT INTO `article`

    We'll start there.

  9. #9
    Join Date
    May 2005
    Location
    United Kingdom / England
    Posts
    557
    Yes i have that.

    PHP Code:
    mysql_query("INSERT INTO article 
    (firstname, surname, locality, username, title, article, submission_date) VALUES('
    $firstname', '$surname', '$locality', '$username', '$title', '$article', '$submission_date')")
    or die(
    mysql_error());

    include 
    "newsview.php"
    So i place "mysql_real_escape_string;" just above "or die(mysql_error());"?
    Kayz

  10. #10
    Join Date
    Feb 2003
    Location
    Canada
    Posts
    958
    Looking at code you posted in another thread, it looks like your query is inside the mysql_query() function.

    SQL injection aside, you could so something like this.

    PHP Code:
    $query sprintf("INSERT INTO article (firstname, surname, username, title, article, submission_date) VALUES('%s', '%s', '%s', '%s', '%s', '%s')"mysql_real_escape_string($firstname), mysql_real_escape_string($surname), mysql_real_escape_string($username), mysql_real_escape_string($title), mysql_real_escape_string($article), mysql_real_escape_string($submission_date));
    mysql_query($query) or die(mysql_error()); 

  11. #11
    Join Date
    May 2005
    Location
    United Kingdom / England
    Posts
    557
    Quote Originally Posted by Adam-AEC View Post
    Looking at code you posted in another thread, it looks like your query is inside the mysql_query() function.

    SQL injection aside, you could so something like this.

    PHP Code:
    $query sprintf("INSERT INTO article (firstname, surname, username, title, article, submission_date) VALUES('%s', '%s', '%s', '%s', '%s', '%s')"mysql_real_escape_string($firstname), mysql_real_escape_string($surname), mysql_real_escape_string($username), mysql_real_escape_string($title), mysql_real_escape_string($article), mysql_real_escape_string($submission_date));
    mysql_query($query) or die(mysql_error()); 
    Yes i am aware of mysql injections but as im sure you've read thats not an issue for me at this time.

    By the way i just would like to say that your a life saver also.

    I quickly googled some examples of "mysql_real_escape_string" and i came out with the result below.

    PHP Code:
    <?php
    // Make a MySQL Connection
    include "../config.php";

    $submission_date date("D M j Y G:i:s");
    $firstname $_POST['firstname'];
    $surname $_POST['surname'];
    $title $_POST['title'];

    ***** 
    $article mysql_real_escape_string($_POST['article']); ********

    $locality $_POST['locality'];

    if((!
    $title) || (!$article)){
       include 
    'fieldmissing.php';
       exit();
    }

    //Insert a row of information into the table "example"
    mysql_query("INSERT INTO article 
    (firstname, surname, locality, username, title, article, submission_date) VALUES('
    $firstname', '$surname', '$locality', '$username', '$title', '$article', '$submission_date')")
    or die(
    mysql_error());

    include 
    "newsview.php";

    ?>
    The above seems to be working fine or do you suppose i use the example you have given me?
    Kayz

  12. #12
    Join Date
    May 2005
    Location
    United Kingdom / England
    Posts
    557
    Also one other thing.

    As the text is being entered it doesn't show paragraph breaks when i hit return. I can see from within the mysql table that the entire text is wrapped where the text is suppose to show in 3 paragraphs.. any ideas how i would be able to achieve this also?
    Kayz

  13. #13
    Join Date
    Aug 2007
    Location
    Greece
    Posts
    390
    well better yet mysql_real_escape() all your vars
    NOT a webhost!helping here just for the fun of it!
    G(r)eek inside.

  14. #14
    Join Date
    Feb 2003
    Location
    Canada
    Posts
    958
    Quote Originally Posted by tix3 View Post
    well better yet mysql_real_escape() all your vars
    I agree, all user supplied data should be escaped (and sanitized but that's a whole other issue).

    I was trying to give you a bit of a real-world usage scenario. Use sprintf to keep your query clean, and provide the insert command with the data it needs separately escaped.

    Ideally it would be in a database object but since this is only for a class, it probably doesn't matter.

    Also one other thing.

    As the text is being entered it doesn't show paragraph breaks when i hit return. I can see from within the mysql table that the entire text is wrapped where the text is suppose to show in 3 paragraphs.. any ideas how i would be able to achieve this also?
    You will probably want to run nl2br() on the text you are outputting from the database. The text field (text area) will be storing the new lines in the database as \n and your browser expects new lines to be defined as <br />

  15. #15
    Join Date
    Feb 2005
    Location
    Poland
    Posts
    248
    You are still supporting old standards, if the guy would use PDO he would not have any issues with data escaping in the first place. Im surprises me that no one pointed that out yet.
    http://www.linkedin.com/in/marcinlulek - my linkedIn profile
    webdeveloper for hire - XHTML, CSS3 PYTHON ,PostgreSQL, Ajax & Javascript, I build apps with Pyramid/Pylons Web frameworks using dojo toolkit for js work

  16. #16
    Join Date
    Mar 2004
    Location
    USA
    Posts
    4,342
    Just safe escape the values that are user-provided.

    Dates/local times shouldn't be.

    Peace,
    Testing 1.. Testing 1..2.. Testing 1..2..3...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •