2

Is it possible to escape a whole query instead of each searched field. For example I know I can do it like this:

$name="O'Connor";
$email="mark.O'Connor@something.com";

$name=mysql_real_escape_string($name);
$email=mysql_real_escape_string($email);

$query =("SELECT * FROM TABLE1 WHERE Name = '$name' OR Email = '$email' ");

// code to run query here

What I am looking for is a way to build my query string like this : 

$query=("SELECT * FROM TABLE1 WHERE Name = '$name' OR Email = '$email' ");
$query=mysql_real_escape_string($query); // Can I escape the whole Query ??

// code to run query here

My reason for asking this Is I have a complex query which is using at around 15 variables from a form and I want to clean them all at once instead of using loads of mysql_real_escape($vairableName)...

Is this possible and can anyone exlplain how I can acheive this

Thanks in advance

Barry Connolly
  • 663
  • 1
  • 6
  • 20
  • 5
    You should rather escape `none` of the query and read about prepared statements. Don't sprinkle sugar over ... :). http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 – Hanky Panky May 20 '14 at 10:20
  • Also, read http://security.stackexchange.com/questions/25684/how-can-i-explain-sql-injection-without-technical-jargon/25710#25710 and ask yourself this question again. – DCoder May 20 '14 at 10:22

2 Answers2

4

You can't do that. Escaping changes characters with special meaning in SQL into escape sequences. You need some characters with special meaning so that the query can work. If you could just escape the entire query, then all database drivers would just do that automatically.

e.g.

$foo = "John O'Reilly";
$sql = "WHERE name='$foo'";
# There are three ' characters here, but only the middle one should be escaped

That said, you should stop using an obsolete database API and pick a modern replacement that supports prepared statements which will save you from having to call real_escape_string all over the place.

Community
  • 1
  • 1
Quentin
  • 914,110
  • 126
  • 1,211
  • 1,335
2

As others have pointed out, using prepared statements is the best way to go. But in case you still want to proceed ahead, you can do something like this:

foreach($_POST as $key=>$value)
{
   // this will take care of escaping all form elements
   $$key = mysql_real_escape_string($value);
}

So if your form contains 3 elements whose names are abc, def, and ghi, the above code will create variables named as $abc, $def, and $ghi respectively.

Then you can use them in query as before:

$query = "SELECT * FROM TABLE1 WHERE Name = '$abc' OR Email = '$def'";
asprin
  • 9,579
  • 12
  • 66
  • 119
  • Thanks for explaining so well Asprin, Really helped me out I will look at the foreach option first then look to use prepared statments in the future , thanks again to all who helped – Barry Connolly May 20 '14 at 11:23
  • mysql*. Do not use mysql_real_escape_string, use: mysqli_real_escape_string instead. – The concise Apr 14 '22 at 07:24