Another way—this one virtually bulletproof—to prevent SQL injections is to use a feature called placeholders. The idea is to predefine a query using ? characters where the data will appear. Then, instead of calling a MySQL query directly, you call the predefined one, passing the data to it. This has the effect of ensuring that every item of data entered is inserted directly into the database and cannot be interpreted as SQL
queries. In other words, SQL injections become impossible.
The sequence of queries to execute when using MySQL’s command line would be like that :
PREPARE statement FROM "INSERT INTO classics VALUES(?,?,?,?,?)"; SET @author = "Emily Brontë", @title = "Wuthering Heights", @category = "Classic Fiction", @year = "1847", @isbn = "9780553212587"; EXECUTE statement USING @author,@title,@category,@year,@isbn; DEALLOCATE PREPARE statement;
The first command prepares a statement called statement for inserting data into the classics table. As you can see, in place of values or variables for the data to insert, the statement contains a series of ? characters. These are the placeholders.
The next five lines assign values to MySQL variables according to the data to be inserted. Then the predefined statement is executed, passing these variables as parameters. Finally, the statement is removed, in order to return the resources it was using.
In PHP, the code for this procedure looks like following Example(assuming that you have created login.php with the correct details to access the database).
<?php require 'login.php'; $db_server = mysql_connect($db_hostname, $db_username, $db_password); if (!$db_server) die("Unable to connect to MySQL: " . mysql_error()); mysql_select_db($db_database) or die("Unable to select database: " . mysql_error()); $query = 'PREPARE statement FROM "INSERT INTO classics VALUES(?,?,?,?,?)"'; mysql_query($query); $query = 'SET @author = "Emily Brontë",' . '@title = "Wuthering Heights",' . '@category = "Classic Fiction",' . '@year = "1847",' . '@isbn = "9780553212587"'; mysql_query($query); $query = 'EXECUTE statement USING @author,@title,@category,@year,@isbn'; mysql_query($query); $query = 'DEALLOCATE PREPARE statement'; mysql_query($query); ?>
Once you have prepared a statement, until you deallocate it, you can use it as often as you wish. Such statements are commonly used within a loop to quickly insert data into a database by assigning values to the MySQL variables and then executing the statement. This approach is more efficient than creating the entire statement from scratch on each pass through the loop.