Getting apostrophes into MySQL table

PHP
1

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):

<!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>

<!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

stripslashes is what you need to use when pulling information from the database and [URL=“http://php.net/manual/en/function.addslashes.php”]addslashes or MySQL escaping is fine for database input

3

if you use addslashes, make sure magic quotes is switched off in your php.ini. Or you can also use

[htmlentities](http://php.net/manual/en/function.htmlentities.php)($str,ENT_QUOTES)
4

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

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 [url=“http://php.net/manual/en/function.mysql-real-escape-string.php”]mysql_real_escape_string is applied to them.

6

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:

<!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. :wink:

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. :smiley:

7

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:

[list][]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[/list]

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?

8

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.prepared-statements.php

9

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.

10

Magic quotes are disabled — see first paragraph of message #4.

11

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 [url=“http://www.php.net/manual/en/filter.filters.sanitize.php”]filter, so you may want to specify FILTER_SANITIZE_STRING for when you’re retrieving string values.


$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.


$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


echo 'Username:&nbsp;&nbsp;' . htmlspecialchars($username) . '<br><br>';
12

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! :cool:

13

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

Using this 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…:

<!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

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 ] )

16

Changing the order of the mysql_query paramaters like so…

$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

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