How to use mysql_real_escape_string function in PHP

Question

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 run mysql_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 of select *\r\nfrom Actor;, hence the error message. Why is mysql_real_escape_string doing this?

Answer 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);