How to use mysql_real_escape_string function in PHP
Solution 1
use it on the actual values in your query, not the whole query string itself.
example:
$username = mysql_real_escape_string($_POST['username']);
$query = "update table set username='$username' ...";
$rs = mysql_query($query);
Solution 2
mysql_real_escape_string()
is the string escaping function. It does not make any input safe, just string values, not for use with LIKE clauses, and integers need to be handled differently still.
An easier and more universal example might be:
$post = array_map("mysql_real_escape_string", $_POST);
// cleans all input variables at once
mysql_query("SELECT * FROM tbl WHERE id='$post[id]'
OR name='$post[name]' OR mtime<'$post[mtime]' ");
// uses escaped $post rather than the raw $_POST variables
Note how each variable must still be enclosed by '
single quotes for SQL strings. (Otherwise the escaping would be pointless.)
Solution 3
Rather than using the outdated mysql extension, switch to PDO. Prepared statement parameters aren't vulnerable to injection because they keep values separate from statements. Prepared statements and PDO have other advantages, including performance, ease of use and additional features. If you need a tutorial, try "Writing MySQL Scripts with PHP and PDO".
Comments
-
Casey Patton over 3 years
So in this program I'm writing, I actually grab a SQL query from the user using a form. I then go on to run that query on my database.
I know not to "trust" user input, so I want to do sanitization on the input. I'm trying to use
mysql_real_escape_string
but have been unsuccessful in getting it to work.Here's what I'm trying, given the input:
select * from Actor;
//"query" is the input string: $clean_string = mysql_real_escape_string($query, $db_connection); $rs = mysql_query($clean_string, $db_connection); if (!$rs) { echo "Invalid input!"; }
This is ALWAYS giving me the
"Invalid input!"
error.
When I take out the
clean_string
part and just runmysql_query
on query, the"invalid input"
message is not output. Rather, when I do this:
$rs = mysql_query($query, $db_connection); if (!$rs) { echo "Invalid input!"; }
It does NOT output
"invalid input".
However, I need to use the
mysql_real_escape_string
function. What am I doing wrong?Update:
Given
select * from Actor;
as an input, I've found the following.Using echo statements I've found that before sanitizing, the string holds the value:
select * from Actor;
which is correct. However, after sanitizing it holds the incorrect value ofselect *\r\nfrom Actor;
, hence the error message. Why ismysql_real_escape_string
doing this?