PHP : How To Prevent SQL Injection

SQL injection is a code injection technique, used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker).
More about SQL Injection

To prevent SQL Injection we have two options, escaping the special characters in the unsafe variable, or using a parameterized query. Both would protect you from SQL injection. The parameterized query is considered the better practice, but escaping characters in your variable will require fewer changes.

Here the example

Example 1 : Escaping Special Characters

$unsafe_variable = $_POST["user-input"];
$safe_variable = mysql_real_escape_string($unsafe_variable);
mysql_query("INSERT INTO table (column) VALUES ('" . $safe_variable . "')");

More about mysql_real_escape_string function.

Warning: As of PHP 5.5.0 mysql_real_escape_string and the mysql extension are deprecated. Please use mysqli extension and mysqli::escape_string function instead

Example 2 : Using Parameterized Query

To use the parameterized query, we need to use MySQLi rather than the MySQL functions. 

$mysqli = new mysqli("server", "username", "password", "database_name");

// TODO - Check that connection was successful.

$unsafe_variable = $_POST["user-input"];

$stmt = $mysqli->prepare("INSERT INTO table (column) VALUES (?)");

// TODO check that $stmt creation succeeded

// "s" means the database expects a string
$stmt->bind_param("s", $unsafe_variable);

$stmt->execute();

$stmt->close();

$mysqli->close();

Whatever the reason, the practice of sql injection is highly detrimental action. This would be a very serious concern for companies with a high level of data confidentiality.

Thank you, hopefully this article useful.

Share it Please

Tweet