Harshad Hirapara Harshad Hirapara - 3 months ago 6x
MySQL Question

How to prevent sql Injection? without modification into an SQL query

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example:

$unsafe_variable = $_POST['user_input'];

mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");

That's because the user can input something like value'); DROP TABLE table;--, and the query becomes:

INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')

What can be done to prevent this from happening?


The absolute minimum you need to do here is escape that variable:

$unsafe_variable = mysql_real_escape_string($_POST['user_input']);

Nothing in your query has to change at that point. This is not necessarily the end of the story, though, as mysql_real_escape_string is not invulnerable and you remain exposed to injection attacks by those using more sophisticated techniques.

The entire mysql_query API has been trashed, it's obsolete and the latest version of PHP no longer supports it. You need to move on to something better, and I'd recommend PDO as a baseline.

The best way to be sure you're doing it right is to use prepared statements with placeholder values. That is your query looks like this:

INSERT INTO table name (column1) VALUES (:column1)

With PDO you can name your placeholders. This makes executing your statement later very easy, you just match up the values:

$stmt->execute(array('column1' => $_POST['user_input'));

So the best way to avoid injection bugs is to avoid injection in the first place. Placeholder values will be substituted correctly, safely, and most important, consistently. All it takes is one mistake where you thought you escaped something but you didn't and people can bust your site wide open.