How can I prevent SQL injection in PHP?

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

<!-- language: lang-php -->

$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?

[1]:

[To see links please register here]

In my opinion, the best way to generally prevent SQL injection in your PHP application (or any web application, for that matter) is to think about your application's architecture. If the only way to protect against SQL injection is to remember to use a special method or function that does The Right Thing every time you talk to the database, you are doing it wrong. That way, it's just a matter of time until you forget to correctly format your query at some point in your code.

Adopting the MVC pattern and a framework like [CakePHP][1] or [CodeIgniter][2] is probably the right way to go: Common tasks like creating secure database queries have been solved and centrally implemented in such frameworks. They help you to organize your web application in a sensible way and make you think more about loading and saving objects than about securely constructing single SQL queries.

[1]:

[To see links please register here]

[2]:

[To see links please register here]

There are so many answers for **PHP and MySQL**, but here is code for **PHP and Oracle** for preventing SQL injection as well as regular use of oci8 drivers:

$conn = oci_connect($username, $password, $connection_string);
$stmt = oci_parse($conn, 'UPDATE table SET field = :xx WHERE ID = 123');
oci_bind_by_name($stmt, ':xx', $fieldval);
oci_execute($stmt);

Whatever you do end up using, make sure that you check your input hasn't already been mangled by `magic_quotes` or some other well-meaning rubbish, and if necessary, run it through `stripslashes` or whatever to sanitize it.

I favor [stored procedures][1] ([MySQL has had stored procedures support since 5.0][2]) from a security point of view - the advantages are -

1. Most databases (including [MySQL][3]) enable user access to be restricted to executing stored procedures. The fine-grained security access control is useful to prevent escalation of privileges attacks. This prevents compromised applications from being able to run SQL directly against the database.
2. They abstract the raw SQL query from the application so less information of the database structure is available to the application. This makes it harder for people to understand the underlying structure of the database and design suitable attacks.
3. They accept only parameters, so the advantages of parameterized queries are there. Of course - IMO you still need to sanitize your input - especially if you are using dynamic SQL inside the stored procedure.

The disadvantages are -

1. They (stored procedures) are tough to maintain and tend to multiply very quickly. This makes managing them an issue.
2. They are not very suitable for dynamic queries - if they are built to accept dynamic code as parameters then a lot of the advantages are negated.

[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

A simple way would be to use a PHP framework like [CodeIgniter][1] or [Laravel][2] which have inbuilt features like filtering and active-record so that you don't have to worry about these nuances.


[1]:

[To see links please register here]

[2]:

[To see links please register here]

I've written this little function several years ago:

function sqlvprintf($query, $args)
{
global $DB_LINK;
$ctr = 0;
ensureConnection(); // Connect to database if not connected already.
$values = array();
foreach ($args as $value)
{
if (is_string($value))
{
$value = "'" . mysqli_real_escape_string($DB_LINK, $value) . "'";
}
else if (is_null($value))
{
$value = 'NULL';
}
else if (!is_int($value) && !is_float($value))
{
die('Only numeric, string, array and NULL arguments allowed in a query. Argument '.($ctr+1).' is not a basic type, it\'s type is '. gettype($value). '.');
}
$values[] = $value;
$ctr++;
}
$query = preg_replace_callback(
'/{(\\d+)}/',
function($match) use ($values)
{
if (isset($values[$match[1]]))
{
return $values[$match[1]];
}
else
{
return $match[0];
}
},
$query
);
return $query;
}

function runEscapedQuery($preparedQuery /*, ...*/)
{
$params = array_slice(func_get_args(), 1);
$results = runQuery(sqlvprintf($preparedQuery, $params)); // Run query and fetch results.
return $results;
}


This allows running statements in an one-liner C#-ish String.Format like:

runEscapedQuery("INSERT INTO Whatever (id, foo, bar) VALUES ({0}, {1}, {2})", $numericVar, $stringVar1, $stringVar2);

It escapes considering the variable type. If you try to parameterize table, column names, it would fail as it puts every string in quotes which is an invalid syntax.

SECURITY UPDATE: The previous `str_replace` version allowed injections by adding {#} tokens into user data. This `preg_replace_callback` version doesn't cause problems if the replacement contains these tokens.

If possible, cast the types of your parameters. But it's only working on simple types like int, bool, and float.

$unsafe_variable = $_POST['user_id'];

$safe_variable = (int)$unsafe_variable ;

mysqli_query($conn, "INSERT INTO table (column) VALUES ('" . $safe_variable . "')");

The simple alternative to this problem could be solved by granting appropriate permissions in the database itself.
For example: if you are using a MySQL database then enter into the database through terminal or the UI provided and just follow this command:

GRANT SELECT, INSERT, DELETE ON database TO username@'localhost' IDENTIFIED BY 'password';

This will restrict the user to only get confined with the specified query's only. Remove the delete permission and so the data would never get deleted from the query fired from the PHP page.
The second thing to do is to flush the privileges so that the MySQL refreshes the permissions and updates.

FLUSH PRIVILEGES;

more information about [flush][1].

To see the current privileges for the user fire the following query.

select * from mysql.user where User='username';

Learn more about [GRANT][2].


[1]:

[To see links please register here]

[2]:

[To see links please register here]

>**Deprecated Warning:**
This answer's sample code (like the question's sample code) uses PHP's `MySQL` extension, which was deprecated in PHP 5.5.0 and removed entirely in PHP 7.0.0.

> **Security Warning**: This answer is not in line with security best practices. [Escaping is inadequate to prevent SQL injection](

[To see links please register here]

), use *prepared statements* instead. Use the strategy outlined below at your own risk. (Also, `mysql_real_escape_string()` was removed in PHP 7.)

Parameterized query AND input validation is the way to go. There are many scenarios under which SQL injection may occur, even though `mysql_real_escape_string()` has been used.

Those examples are vulnerable to SQL injection:

$offset = isset($_GET['o']) ? $_GET['o'] : 0;
$offset = mysql_real_escape_string($offset);
RunQuery("SELECT userid, username FROM sql_injection_test LIMIT $offset, 10");
or

$order = isset($_GET['o']) ? $_GET['o'] : 'userid';
$order = mysql_real_escape_string($order);
RunQuery("SELECT userid, username FROM sql_injection_test ORDER BY `$order`");

In both cases, you can't use `'` to protect the encapsulation.

[Source][1]: *The Unexpected SQL Injection (When Escaping Is Not Enough)*

[1]:

[To see links please register here]