How can I prevent SQL injection in PHP?

Asked  Jun 03, 09:48 pm 47 Views Add Your Answer
Asked  Jun 03, 09:48 pm

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?

0 Dislikes
0 Comments

1 Answers

Edited  Jun 25, 04:41 pm

To use the parameterized query, you need to use either Mysqli or PDO. To rewrite your example with mysqli, we would need something like the following.

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("server", "username", "password", "database_name");

$variable = $_POST["user-input"];
$stmt = $mysqli->prepare("INSERT INTO table (column) VALUES (?)");
// "s" means the database expects a string
$stmt->bind_param("s", $variable);
$stmt->execute();

The key function you'll want to read up on there would be mysqli::prepare .

Also, as others have suggested, you may find it useful/easier to step up a layer of abstraction with something like PDO.

Please note that the case you asked about is a fairly simple one and that more complex cases may require more complex approaches. In particular:

  • If you want to alter the structure of the SQL based on user input, parameterized queries are not going to help, and the escaping required is not covered by mysql_real_escape_string. In this kind of case, you would be better off passing the user's input through a whitelist to ensure only 'safe' values are allowed through.

0 Likes 0 Dislikes
0 Comments
×
Google login
SignUp with Email
X