User provided input SQL-escaping

Question

Can you please give me advise? I searched for questions but did not found something similiar to mine.

How do i make my user inputs automatically escaped when they are intended to use in SQL queries? I don't like in my code filled with something like

$var_x = $DB->Escape($_POST['var_x']);
$another_var = $DB->Escape($_POST['another_var']);
$some_string = $DB->Escape($_POST['some_string']);
...

*Assuming i have Database class with Escape method which performs mysql_real_escape_string*

But i can't set auto escape on SQL query as well, because it breaks insert queries:

function Exec($sql){
   $result = mysql_query($this->Escape($sql));
}

$q = $DB->Exec("SELECT * FROM table WHERE id = 'xxx'");

It makes them \'xxx\'. which is incorrect.

Last thing i want to do is make parameterized statements, as it will make system more complicated. I'll consider this option when nothing else will left.

In short - how to make smart auto-escape which works with whole query and escapes only values?

Answer 1

In fact, the one and the only thing you should ever consider (we're not speaking of test projects here, obviously) is to use parameterized statements. This is the only way (when SQL synax allows for them, of course). Properly done, these won't make your system more complicated, but they will make it more robust.

Answer 2

I use this method:

$db->set('name',$name);
$db->set('title',$title);
$db->insert('users');

Here the $db->set() method automatically escapes the value (i.e the 2nd argument).

With PHP 5, you can also do this:

$db->set('name',$name)->set('title',$title)->insert('users');

Answer 3

Sorry, you can't auto anything. When you are concatenating strings, you will always have character escaping issues there; this is not something you can ‘solve’ once and never have to think about again.

If you do:

$a= "a'b";
$query= "SELECT * FROM things WHERE name='$a'";

Then your query string contains both apostrophes that are real apostrophes in string literals, and apostrophes that are string delimiters:

SELECT * FROM things WHERE name='a'b'

One needs to be escaped; the other mustn't. How can you tell which is which? You can't, that information is lost forever. You must instead say:

$query= "SELECT * FROM things WHERE name='".mysql_real_escape_string($a)."'";

(or whichever other escape function is more suitable for the database you're using.)

Ugly? Hell yeah. That's why we use parameterised queries instead. This gets away from string concatenation with all its woes.

String concatenation looks easy. People think they understand clodging strings together. But really, they don't.

Answer 4

Last thing i want to do is make parameterized statements, as it will make system more complicated. I'll consider this option when nothing else will left.

Care to explain your assertion that parametrized queries are "more complicated"? I don't concatenate external data into queries - ever - and it's the simplest thing in the world to do, as well as being far closer to "bulletproof" than any escaping technique is ever likely to be.