SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2006
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Getting apostrophes into MySQL table

    I'm building a web form to save user inputs into a MySQL database table and return the formatted results to the user as a web page. The following are stripped-down examples of the HTML and PHP files, showing insertion to just two columns of apostrophe_test, user (varchar) and comments (text):
    Code:
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
    "http://www.w3.org/TR/html4/strict.dtd">
    <html>
    <head>
    <title>
    apptest.html
    </title>
    <meta http-equiv="content-type" content="text/html;charset=utf-8">
    </head>
    <body>
    
    <form action="apptest.php" method="post">
    
    Username:&nbsp;&nbsp;<input type="text" name="username" size="20" maxlength="20">
    <br><br>
    
    Comments:&nbsp;<textarea name="comments" rows="2" cols="30"></textarea>
    <br><br>
    
    <input type="submit" value="Submit">&nbsp;&nbsp;<input type="reset" value="Reset">
    
    </body>
    </html>
    Code:
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
    "http://www.w3.org/TR/html4/strict.dtd">
    <html>
    <head>
    <title>
    apptest.php
    </title>
    <meta http-equiv="content-type" content="text/html;charset=utf-8">
    </head>
    <body>
    <?php
    
    $username = htmlentities($_POST['username']);
    $comments = htmlentities($_POST['comments']);
    
    $connection = mysqli_connect('localhost', '********', '********', '********')
    or die('Error connecting to MySQL server.');
    
    $query = "INSERT INTO apostrophe_test (username, comments) VALUES ('$username', '$comments')";
    
    $result = mysqli_query($connection, $query)
    or die('Error querying database.');
    
    mysqli_close($connection);
    
    echo 'Username:&nbsp;&nbsp;' . $username . '<br><br>';
    echo 'Comments:&nbsp;' . $comments;
    
    ?>
    </body>
    </html>
    The form works fine, storing inputs (with any double quotes, ampersands, etc. formatted as entities) into the database and returning the inputs as originally entered back to the user, EXCEPT...

    If an input includes an apostrophe, such as a contraction like don't, I immediately get a blank screen with "Error querying database" and nothing is added to the database. If I "escape" the apostrophe/single quote by typing don\'t, the input is accepted and appears in the database as don't (without the backslash) but appears in the return web page as don\'t (with the backslash).

    Since the form is for public input, users need to be able to enter their comments in the accustomed manner (without any escape characters) and see a normal-looking return page. It's fine if the database needs to use entities or whatever to store comments properly.

    I realize this is a well-known issue and a Google search displays many links with various remedies, but my current knowledge is not up to the task of understanding everytlhing that's being suggested. What would be a straightforward, effective way to modify the above code so regular apostrophes (the lower case key next to Enter) will be accommodated by MySQL? Thanks!

  2. #2
    SitePoint Wizard bronze trophy chris.upjohn's Avatar
    Join Date
    Apr 2010
    Location
    Melbourne, AU
    Posts
    2,197
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    stripslashes is what you need to use when pulling information from the database and addslashes or MySQL escaping is fine for database input

  3. #3
    Non-Member
    Join Date
    Jan 2011
    Location
    Heaven
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if you use addslashes, make sure magic quotes is switched off in your php.ini. Or you can also use
    Code:
    htmlentities($str,ENT_QUOTES)

  4. #4
    SitePoint Enthusiast
    Join Date
    May 2006
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the replies, but as a PHP novice, I need more help. I've seen reference to addslashes, stripslashes, mysql_real_escape_string, and other functions many places on the web, but in spite of reading what the PHP Manual has to say, I still don't understand specifically how to modify the code I posted to get the result I need (magic quotes are disabled on my server).

    For example, where would I put htmlentities($str,ENT_QUOTES)? Does it replace something already there? And wouldn't I need to define/declare $str first?

    Using addslashes for inputs to the database and stripslashes for pulling info out of the database sounds like a logical approach, but what's the best way to implement that? Do I need to declare some new variables, one set to input data and another set to pull it out for display? I could probably muddle around and find something that works, but I'm also interested in "best practices" and clean code.

    Thanks again!

  5. #5
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,729
    Mentioned
    104 Post(s)
    Tagged
    4 Thread(s)
    The following post provides some details on the whole Input/Output escaping process.

    Handling Input and Output

    The fundamental idea is for the values to be unescaped raw values throughout most of the PHP code, so when they are output to different places, such as xml, database, html, mail, etc.. that they can be properly escaped when they are output to each appropriate destination.

    As an example, the mysql_query documentation page shows in example #2 how to correctly escape the values going to the database, which relies on the provided values being completely unescaped before mysql_real_escape_string is applied to them.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  6. #6
    SitePoint Enthusiast
    Join Date
    May 2006
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've modified my php file like so, and it seems to work fine every character goes into the database without error, and info pulled out of the database displays properly:
    Code:
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
    "http://www.w3.org/TR/html4/strict.dtd">
    <html>
    <head>
    <title>
    apptest.php
    </title>
    <meta http-equiv="content-type" content="text/html;charset=utf-8">
    </head>
    <body>
    <?php
    
    $username = htmlentities($_POST['username']);
    $comments = htmlentities($_POST['comments']);
    
    $usernameIn = addslashes($username);
    $commentsIn = addslashes($comments);
    
    $usernameOut = stripslashes($usernameIn);
    $commentsOut = stripslashes($commentsIn);
    
    $connection = mysqli_connect('localhost', 'xxxxxx', 'xxxxxx', 'xxxxxx')
    or die('Error connecting to MySQL server.');
    
    $query = "INSERT INTO apostrophe_test (username, comments) VALUES ('$usernameIn', '$commentsIn')";
    
    $result = mysqli_query($connection, $query)
    or die('Error querying database.');
    
    mysqli_close($connection);
    
    echo 'Username:&nbsp;&nbsp;' . $usernameOut . '<br><br>';
    echo 'Comments:&nbsp;' . $commentsOut . '<br><br>';
    
    ?>
    </body>
    </html>
    But the actual form I'm working on has about 50 fields rather than the 2 in the example, and it would seem rather excessive/inefficient to declare 150 variables rather than 50. Could arrays be used to apply addslashes to all 50 fields in one statement, then remove them all in a second statement? I've tried that approach with no success, but I imagine some of you could do it in your sleep.

    Could I please ask any who respond to actually show how my code might be changed, rather than saying "use xxxxxx" or linking to a section of the PHP Manual? I'll be sure to return the favor to new novices when I get a handle on this.

  7. #7
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,729
    Mentioned
    104 Post(s)
    Tagged
    4 Thread(s)
    First, what is your situation. By that I mean, are magic quotes currently enabled on your server?

    If they are, you need to undo the damage that it does, by one of these methods:
    • Tell your server to stop using magic quotes
    • Tell PHP in the .htaccess file to turn off magic quotes
    • Disable magic quotes at runtime by running some PHP code
    • Undo the magic quotes from each of your retrieved value individually


    It's your choice as to which method is employed, but the amount of effort and problems grow as you go lower on that list.

    Full details on the first two options are (I'm sorry) at the Disabling Magic Quotes documentation page. Do you want us to quote for you here what they have to say there?
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  8. #8
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    693
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by recce101 View Post
    What would be a straightforward, effective way to modify the above code so regular apostrophes (the lower case key next to Enter) will be accommodated by MySQL? Thanks!
    Use PDO prepared statements. The quote/escape problem goes away, your code becomes simpler and sql injection becomes considerably less likely.

    http://www.php.net/manual/en/pdo.pre...statements.php

  9. #9
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,729
    Mentioned
    104 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by ahundiak View Post
    Use PDO prepared statements. The quote/escape problem goes away, your code becomes simpler and sql injection becomes considerably less likely.

    http://www.php.net/manual/en/pdo.pre...statements.php
    Agreed, that is a good solution, though it does still requires that magic quotes not be in the data that's passed through to it.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  10. #10
    SitePoint Enthusiast
    Join Date
    May 2006
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Magic quotes are disabled see first paragraph of message #4.

  11. #11
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,729
    Mentioned
    104 Post(s)
    Tagged
    4 Thread(s)
    With magic quotes being disabled, you don't need to handle the input much more than to validate or sanitize the values.

    Input

    If you use filter_input then that can be very easy. By default it applies the unsafe_raw filter, so you may want to specify FILTER_SANITIZE_STRING for when you're retrieving string values.

    Code php:
    $username = filter_input(INPUT_POST, 'username', FILTER_SANITIZE_STRING);

    If it's an email, you would instead use FILTER_SANITIZE_EMAIL
    Numbers and other types have their own sanitize filter too.

    If you're intending to validate the values instead, you can use the validate filters instead, which can help to reduce the amount of work that you need to do.

    Output to Database

    When it comes to passing those values to the database, use the technique that's appropriate for how you're communicating with the database. If you're using mysql methods, then use the techniques as presented in Example #2 on the mysql_query page.

    Code php:
    $query = sprintf('INSERT INTO apostrophe_test (username, comments) VALUES ("%s", "%s")',
        mysql_real_escape_string($username),
        mysql_real_escape_string($comments)
    );

    Output to HTML

    When you're outputting to the HTML page, and you don't want special characters to be potentially interpreted by the page as code, but to instead be shown as a string, you should pass the raw string through htmlspecialchars

    Code php:
    echo 'Username:&nbsp;&nbsp;' . htmlspecialchars($username) . '<br><br>';
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  12. #12
    SitePoint Enthusiast
    Join Date
    May 2006
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Full details on the first two options are (I'm sorry) at the Disabling Magic Quotes documentation page. Do you want us to quote for you here what they have to say there?
    Thanks, but I have no problem finding and reading the links. In fact, I read that particular one yesterday, and that's why I ran the test and confirmed magic quotes are disabled. The difficulty is, while I'm fairly competent with CSS and HTML, I'm quite new to PHP, and a lot of the terminology and examples don't yet make much sense. That's why I keep making requests like this...

    Could I please ask any who respond to actually show how my code might be changed, rather than saying "use xxxxxx" or linking to a section of the PHP Manual? I'll be sure to return the favor to new novices when I get a handle on this.
    ...in hopes I can at least get on the right track. Believe me, once I get over a couple of hurdles like this, I will no longer be a pest!

  13. #13
    SitePoint Enthusiast
    Join Date
    May 2006
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My apologies, pmw57, I think we're getting there with your last post. I'll play with that some tonight and see how it goes. Thanks!

  14. #14
    SitePoint Enthusiast
    Join Date
    May 2006
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Using this code:

    Code:
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
    "http://www.w3.org/TR/html4/strict.dtd">
    <html>
    <head>
    <title>
    apptest.php
    </title>
    <meta http-equiv="content-type" content="text/html;charset=utf-8">
    </head>
    <body>
    <?php
    
    $username = filter_input(INPUT_POST, 'username', FILTER_SANITIZE_STRING);
    $comments = filter_input(INPUT_POST, 'comments', FILTER_SANITIZE_STRING);
    
    $connection = mysqli_connect('localhost', 'xxxxxx', 'xxxxxx', 'xxxxxx')
    or die('Error connecting to MySQL server.');
    
    $query = sprintf("INSERT INTO apostrophe_test (username, comments) VALUES ('%s', '%s')",
        mysql_real_escape_string($username),
        mysql_real_escape_string($comments)
    );
    
    $result = mysqli_query($connection, $query)
    or die('Error querying database.');
    
    mysqli_close($connection);
    
    echo 'Username:&nbsp;&nbsp;' . htmlspecialchars($username) . '<br><br>';
    echo 'Comments:&nbsp;&nbsp;' . htmlspecialchars($comments) . '<br><br>';
    
    ?>
    </body>
    </html>
    I get this:

    Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'recce101'@'localhost' (using password: NO) in /home/recce101/public_html/testremote/apptest.php on line 29

    Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in /home/recce101/public_html/testremote/apptest.php on line 29

    Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'recce101'@'localhost' (using password: NO) in /home/recce101/public_html/testremote/apptest.php on line 30

    Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in /home/recce101/public_html/testremote/apptest.php on line 30
    Username:

    Comments: ! @ # $ % ^ & * ( ) _ + { } | : " ? - = [ ] \ ; ' , . /

    (The input contained all of my keyboard's special characters.)

    And if I change the 3 mysqli... functions to mysql...:

    Code:
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
    "http://www.w3.org/TR/html4/strict.dtd">
    <html>
    <head>
    <title>
    apptest.php
    </title>
    <meta http-equiv="content-type" content="text/html;charset=utf-8">
    </head>
    <body>
    <?php
    
    $username = filter_input(INPUT_POST, 'username', FILTER_SANITIZE_STRING);
    $comments = filter_input(INPUT_POST, 'comments', FILTER_SANITIZE_STRING);
    
    $connection = mysql_connect('localhost', 'xxxxxx', 'xxxxxx', 'xxxxxx')
    or die('Error connecting to MySQL server.');
    
    $query = sprintf("INSERT INTO apostrophe_test (username, comments) VALUES ('%s', '%s')",
        mysql_real_escape_string($username),
        mysql_real_escape_string($comments)
    );
    
    $result = mysql_query($connection, $query)
    or die('Error querying database.');
    
    mysql_close($connection);
    
    echo 'Username:&nbsp;&nbsp;' . htmlspecialchars($username) . '<br><br>';
    echo 'Comments:&nbsp;&nbsp;' . htmlspecialchars($comments) . '<br><br>';
    
    ?>
    </body>
    </html>
    I get this:

    Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/recce101/public_html/testremote/apptest.php on line 35
    Error querying database.

    ???

  15. #15
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,729
    Mentioned
    104 Post(s)
    Tagged
    4 Thread(s)
    Regarding the mysqli - you don't appear to have it enabled on your server.

    On the mysql - your mysql_query statement has its parameters around the wrong way.
    Please refer to the mysql_query documentation page for how it should be done.

    resource mysql_query ( string $query [, resource $link_identifier ] )
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  16. #16
    SitePoint Enthusiast
    Join Date
    May 2006
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Changing the order of the mysql_query paramaters like so...
    Code:
    $result = mysql_query($query, $connection)
    or die('Error querying database.');
    ...at least eliminates the warning error, but still produces "Error querying database." This...

    resource mysql_query ( string $query [, resource $link_identifier ] )

    ...doesn't give me any understandable clues on what else to do.

    Maybe it's time to change tactics. I've tried a couple of online tutorials, but which one(s) would you recommend for learning PHP best practices and integrating PHP with MySQL? Thanks.

  17. #17
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    693
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by recce101 View Post
    Changing the order of the mysql_query paramaters like so...
    Code:
    $result = mysql_query($query, $connection)
    or die('Error querying database.');
    ...at least eliminates the warning error, but still produces "Error querying database." This...

    resource mysql_query ( string $query [, resource $link_identifier ] )

    ...doesn't give me any understandable clues on what else to do.

    Maybe it's time to change tactics. I've tried a couple of online tutorials, but which one(s) would you recommend for learning PHP best practices and integrating PHP with MySQL? Thanks.
    http://net.tutsplus.com/tutorials/ph...tabase-access/

    There are very few valid reasons why a newcomer should invest time learning the php mysql interface.


Bookmarks

Posting Permissions

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