Preventing MySQL Injection with PHP

SQL injection refers to the act of someone inserting a MySQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.

We can get two expected responses with a correct injection inserted, one is when the sentence is returning the same result withouth injection, and other with blank results, error page or redirection, or defaulted. The results will be diferent if we use AND or OR sintax in the injected sentence. Let me explain:

- OR sintax: could be used to display multiple results from an abused injection.
- AND sintax: could be used to guess values, as deterministic operator.

It would be usefull to determinate different attack vectors in MySQL injection to stress the database and get it's structure:

- The use of INTO OUTFILE, or LOADFILE. The use of files will help a lot in several attack vectors.

SQL Injection Example

$name = "Sachin";
$query = "SELECT * FROM Players WHERE name= '$name'";
echo "Ordinary: " . $query . "
";

// user input that uses SQL Injection
$name_bad = "' OR 1'";

// our MySQL query builder, however, not a very safe one
$query_bad = "SELECT * FROM Players WHERE name= '$name_bad'";

// display what the new query will look like, with injection
echo "Injection: " । $query_bad;

Output


Normal: SELECT * FROM Players WHERE name= 'sachin

Injection: SELECT * FROM Players WHERE name= '' OR 1''

For Prevention - mysql_real_escape_string()

//NOTE: you must be connected to the database to use this function!
// connect to MySQL

$name_bad = "' OR 1'";

$name_bad = mysql_real_escape_string($name_bad);

$query_bad = "SELECT * FROM players name= '$name_bad'";
echo "Escaped Bad Injection: 
" . $query_bad . "
";


$name_evil = "'; DELETE FROM players 1 or name= '";

$name_evil = mysql_real_escape_string($name_evil);

$query_evil = "SELECT * FROM Players WHERE  name = '$name_evil'";
echo "Escaped Evil Injection: 
" । $query_evil;

Notice that those evil quotes have been escaped with a backslash \, preventing the injection attack. Now all these queries will do is try to find a username that is just completely ridiculous:

• Bad: \' OR 1\'
• Evil: \'; DELETE FROM customers WHERE 1 or username = \'