mysql real escape string + php data check

data integrity checks

[rodriguez1804]

Hey all,

I am just wondering if I am doing this right. I have a form for user registration (username, password, etc.) and then I do a data check on the user input values to be inserted into the database. This is what I do:

$username=mysql_real_escape_string(htmlspecialchars(strip_tags(($_POST['username']))));
insert into DB

Now, if user types as username: jack'sCar, an echo after data check shows as jack\'sCar, BUT in the database, it shows as jack'sCar? Isn't it supposed to show as jack\'sCar in the DB also? Is the user input data pretty safe after using the above method? Thanks.

[andrewsmd]

You are doing everything correct. The reason the echo show like that is because it wouldn't insert otherwise. The \ is telling the DB to escape the '. Here's an example.
If you didn't use the escape_string method and tried to insert that the query would look like this to the DB.
insert into users values 'jack'sCar'; the DB would error because the original ' would be telling the DB that the string stops there when it really doesn't so the command
insert into users values 'jack\'sCar'; is telling the DB hey this is not the end this is part of the string so insert it as text and don't read it as syntax. Does that make any sense?

[andrewsmd]

One last thing, if you literally wanted jack\'sCar in the database the sql query would look like
insert... values 'jack\\\'sCar';
You need one \ to escape a slash and another to escape the '

[idfer]

Be careful, with a few exceptions, it's not a good idea to encode your data for html before inserting it into the database. First of all, there is no need for it from SQL's point of view, second, you make your data larger for no reason so you'll end up creating DB fields larger than necessary, and if you ever want to output the data to a csv file, plain text, or some other format, you'll have to carefully decode the data first.

Best thing is to encode the data on the fly as you're outputing it to various targets, e.g. to insert into DB:

$qh = mysql_query("insert into DB values('". mysql_real_escape_string($_POST['username']) . "')");

To output to HTML:

echo "You're logged in as: " . htmlentities($username);

URL:

echo '<a href="' . htmlentities('/edituser?name=' . urlencode($username)) . '">';

Also about strip_tags... if you pass all data through htmlentities (as i mention above) it's not absolutely necessary to call strip_tags. But if the data is from user input, and you want to disallow certain characters or strings of characters, it's more user-friendly to tell the user and give them a chance to correct the problem instead of quietly stripping the unwanted chars.

Finally, it's good practice to check for length of string. If the username field in the DB is varchar(20), make sure $_POST['username'] is not longer than 20 (check the length before you pass it through mysql_real_escape_string).

Oh and test, test, test, then test again. :) Write some raw HTML pages or PHP curl scripts that call your real PHP scripts with every value possible: something with a single-quote, something with a double-quote, with a space (may cause trouble in numeric fields), with a new-line, with an html tag, a super long value etc.