Which MySQL Query is more secure? Part 2

Question

Which query is more EXTRA secure?

I have even heard salt(); and md5(); in php5 would been extra secure for mySQL insertion.

$customers_email = mysql_real_escape_string(trim(strtolower($_REQUEST['customers_email'])));    
$customers_email = mysql_real_escape_string(($_REQUEST['customers_email']));

Or even this, I thought of:

$1=$_REQUEST['customers_email'];
$2=$1;
$3=$2;
$4=$3;
$5=$4;
$a=$5;

$xx = mysql_real_escape_string(($a));

My original thread: https://stackoverflow.com/questions/7633993/php-mysql-select-how-to-make-it-secure

Answer 1

This question has been asked many many times before.

It's always best to use PDO, but if you have to use the mysql_* lib, do it like this:

Escaping for parameters
The following code is 100% secure:

$var = mysql_real_escape_string($_POST['var']);  
$query = "SELECT * FROM table1 WHERE afield = '$var' "
// You must quote your $vars                  ^    ^
// failure to do so defeats the escaping and causes syntax errors.

No further action is needed to make the code SQL-injection same.

Escaping for dynamic column/table names
This code:

Unsafe, does not work

$afield = mysql_real_escape_string('$_POST['var']);  
$query = "SELECT * FROM table1 WHERE `$afield` = 1 "
//You can only use escaping for values, never for dynamic SQL.

This will not work, you need to check database, table and column names against a whitelist.

100% Safe for dynamic SQL

$allowed_fields = array('field1', 'field2');
$afield = $_POST['afield'];
if (in_array($afield, $allowed_fields)) {
    $query = "SELECT * FROM table1 WHERE `$afield` = 1";
}

About hashing and salting
You only use salted hashes for passwords.
It is a bad idea to store unencrypted passwords in a database, instead you store a hashed password.
In order to defeat rainbow tables you have to salt the hash with a different semi-random salt; the salt does not need to be secret, it just needs to be somewhat random.

//To add a user.
INSERT INTO users (name, salt, passhash) 
VALUES ('$username', '$salt', SHA2(CONCAT('$salt','$password'),512));

//To check a password:
SELECT u.id FROM users u 
WHERE u.name = '$username' 
  AND passhash = SHA2(CONCAT(salt, '$password'),512);

//To change a password:
UPDATE users u SET passhash = SHA2(CONCAT(s.salt, '$newpassword'),512)
WHERE u.id = (SELECT u2.id FROM (
  SELECT u1.id FROM users u1 
  WHERE u1.name = '$username' 
  AND passhash = SHA2(CONCAT(u1.salt, '$oldpassword'),512) u2) s

See also:
How to prevent SQL injection with dynamic tablenames?
How does the SQL injection from the "Bobby Tables" XKCD comic work?
How can I prevent SQL injection in PHP?

Answer 2

None of your queries are more secure than the other. One of them converts the string to all lowercase and trims whitespace. This doesn't do anything to prevent a SQL injection. Your last query is the same as a plain mysql_real_escape_string($_REQUEST['customers_email']);.

Answer 3

Each of your first approaches are as secure as each other because they're using the mysql_real_escape_string function. SQL injection works by 'breaking out' of your query by introducing additional quote marks into your string that then break your conditions.

Consider:

$email = $_GET['email'];
$sql = "SELECT * FROM users WHERE email='$email'";

If an attacker were to submit an email of ' OR 1=1 -- your query after string interpolation would become:

$sql = "SELECT * FROM users WHERE email='' OR 1=1 -- '";

In this case, the double dash in MySQL is a comment, commenting our your original right quote mark. By running mysql_real_escape_string, the database engine will properly escape the email string to prevent the quote mark in it 'breaking out' of your query.

The exact escaping style of each database engine is different, so each engine has their own set of escape functions. For this reason, it would be wise to switch to using PDO and prepared statements as mentioned by the others here.

I hope this has explained this a little further.