Where should I place mysql_real_escape_string? [cl

Question

What's the best way to use mysql_real_escape_string, is it at the beginning like this:

$email = mysql_real_escape_string($_POST['email']); 
$qemail = mysql_query ("SELECT email FROM ppl WHERE email='$email'"); 

or at the end like this:

$email = $_POST['email'];
$qemail = mysql_query ("SELECT email FROM ppl WHERE email='". mysql_real_escape_string($email) ."'");

The whole website is using mysql so I have to keep it in mysql. The problem is, I don't want to use mysql_real_escape_string everywhere (the code looks confusing and horrible). I would like to use it only at the beginning for $_POST, but is that enough?

Some people suggest that it's best to use it in queries, but I fail to see why.

Answer 1

You should place mysql_real_escape_string() directly into the rubbish bin and migrate to mysqli or PDO and learn to use prepared statements instead.

I've mentioned this before in A Gentle Introduction to Application Security, but the fundamental problem that makes SQL injection possible is the confusion of data and code.

Prepared statements send your query string (SELECT * FROM foo WHERE column = ?) and your parameters (['foo']) in separate packets to the database server. The parameters never get a chance to touch the query string, thus preventing the condition that makes SQL Injection possible in the first place.

Escaping inputs and building the query string does not have the same guarantee. It's possible to do it safely, of course, but if you make one mistake and an unskilled hacker finds it, your entire database is toast. (Keep in mind, SQL Injection is low-hanging fruit.)

TL;DR - Just use Prepared Statements.

Answer 2

Switch to a better solution other than mysql_ .

That being said, if you have to use the deprecated mysql_, I suggest you use sprintf() for readability and ease of use:

$qemail = sprintf('SELECT email FROM ppl WHERE email="%s"',
                  mysql_real_escape_string($_POST['email'])
                  );

$qr = mysql_query($qemail);

If you have more than one parameter you can have multiple %s and other tags, see sprintf() documentation:

$str_qr = sprintf('SELECT * FROM table, WHERE val="%s", val2="%s", someIntegerField=%d',
      mysql_real_escape_string($val1), 
      mysql_real_escape_string($val2),
      mysql_real_escape_string($someNumberId),
);

$qr = mysql_query($str_qr);