What is a SQL Injection?
SQL injection is a technique to attack database based applications. The attacker inserts SQL statements into an input field of the application for execution by the database. An application with a vulnerability can be used by an attacker to dump the contents of a database, delete the database contents, insert HTML code to perform Cross-site scripting (XSS) attack, and much more.
Example SQL injection in PHP/MySQL
$_GET['student_id']='0; DELETE FROM students WHERE 1';
$q = "UPDATE students SET student_fname='$_GET[student_fname]' WHERE student_id=$_GET[student_id]";
$res = mysql_query($q);
This is a very simple example, but this example would delete the entire contents of the students database.
How can I prevent this sql injection attack in my PHP/MySQL application?
Use prepared statements and parameterized queries
With prepared statements and parameterized queries the SQL queries are sent to the database separately from the data. The SQL statements are parsed and compiled by the database server and you use placeholders like "?" or ":name" to specify the parameters (Depends on what functions/libraries you use, see examples below). Then when the query is executed the parameters are combined with the compiled SQL query. Since the parameters are combined with the compiled query instead of a SQL string there is no way to trick the query to execute arbitrary code and this prevents the sql injection.
PHP1234$mysqli = new mysqli("localhost", "username", "password", "database");$stmt = $mysqli->prepare("SELECT * FROM students WHERE student_fname=?");$stmt->bind_param("s", $_GET['student_fname']);$stmt->execute();
Use PDO Extension:
PHP12345$pdo = new PDO('mysql:host=localhost;dbname=database', "username", "password");$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);$stmt = $pdo->prepare("SELECT * FROM students WHERE student_fname=:student_fname");$stmt->execute(array('student_fname' => $_GET['student_fname']));
Escaping the special characters in your data
mysql_connect("localhost", "username", "password");
$student_fname = mysql_real_escape_string($_GET['student_fname']);
$res = mysql_query("SELECT * FROM students WHERE student_fname='$student_fname'");