Filtering out MySQL query in PHP

Question

Hey there, I'm doing some queries to a MySQL database that involves some user-input.. And I was wondering how to prevent injection attacks, like, for example, if someone were to type

"; a-MySQL query here;"

It would be executed, this would allow people access to compromise my system. I'm wondering how i could protect against things like this, possibly create a function that filters out the query looking for bad statements? or perhaps not allowing the ; character?

To sum it all up:

• Whats the common practice now adays to prevent injection attacks?
• How should I go about it?
• How effective will this solution be against people who know what they are doing from changing my database.

Answer 1

They best solution is to run every piece of input from the wild (user) through this function PHP.net: MySQL Real Escape String This will clean up most - if not all - Injection issues seen today. You would simply need to do something like the following:

$var = mysql_real_escape_string($_GET['var']);
$query = "INSERT INTO foo (var) VALUES (\"$var\");

It's always good practice to force type casting on variables you know should be a type. For instance a numerical identifier:

$id = (INT)$_GET['id'];

or

$id = ( is_numeric($_GET['id']) ? (INT)$_GET['id'] : -1; // replace -1 with FALSE, or NULL, etc

Which will force that variable to be an Integer so you won't end up with $id being "foo" or some other non-numeric.

Answer 2

The only way is to properly escape user-submitted data. Others have pointed out some ways of doing so.

There's another way: prepared statements and placeholders. Prepared statements are supported by every modern PHP database interface, including mysqli and PDO.

Let's use PDO as a demonstration. Let's say we wanted to update a bit of data in the table foo submitted by a user.

$sql = 'UPDATE foo SET bar = ? WHERE user_id = ?';
$sh = $db->prepare($sql);
$sh->execute(array( $_POST['bar'], $_SESSION['user_id'] ));

The variables in the array passed to execute replace the question mark placeholders in the query. When this happens, they are automatically escaped and quoted. You don't need to manually escape them to make them safe to put in the database!

On the other hand, you will still need to filter them for unexpected content, like HTML, Javascript, letters where you expect numbers, etc. Making data safe to insert into the database is only half of the battle.

Answer 3

An even better way than calling a mysql_escape_string variant is to use bound queries in PDO, so that it's impossible to forget or miss a case since PDO will do the escaping for you when circumstances require it.

See for more: http://www.electrictoolbox.com/php-pdo-bound-placeholders/