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