Provide a definition and example of a prepared statement in PHP, Java, and Perl. What are the advantages of using prepared statements? How do prepared statements help prevent SQL injection attacks?
Prepared statements, also known as parameterized statements or parameterized SQL, can be thought of as a template for SQL statements. Prepared statements allow database engines to run SQL statements more efficiently because the same (or similar) SQL is used over and over again – we’ll explain more about the details below. The key feature of a prepared statement is the fact that values can be plugged into the query after the query is “prepared”, and ready to be executed. This will make more sense when you see the examples below.
Prepared Statements use Placeholders
Prepared statements use question marks (?), which are placeholders for where actual values that will be used in the SQL should be “plugged” in. The placeholders used in prepared statements are also known as bound parameters, since they are essentially parameters that are passed to the SQL that “bind” to the SQL at a later time.
Confused yet? Well, some examples should clear it up – it’s really not difficult to understand at all.
Examples of Prepared Statements
Below we present some examples of prepared statements in Java, PHP, and Perl. Here we are using the interface libraries that each language provides to communicate with different database environments (like MySQL, Oracle, etc). As you may already know, Java uses a library known as JDBC, PHP uses something called PDO (PHP Data Objects), and Perl uses something called the Perl DBI (Perl Database Interface)
Example of a prepared statement in Java using JDBC:
java.sql.PreparedStatement stmt = connection.prepareStatement( "SELECT * FROM table WHERE EMAIL = ?"); /* The statement below sets "?" to an actual value that is stored in the email variable, we are also assuming that the email variable is set beforehand: */ stmt.setString(1, email); stmt.executeQuery();
Example of a prepared statement in PHP using PDO:
$stmt = $dbh->prepare("SELECT * FROM table WHERE EMAIL = ? "); /* The statement below sets "?" to an actual value that is stored in the email variable, we are also assuming that the $email variable is set beforehand: */ $stmt->execute($email);
Example of a prepared statement in Perl using Perl DBI:
my $stmt = $dbh->prepare('SELECT * FROM table WHERE EMAIL = ?'); /* The statement below sets "?" to an actual value that is stored in the email variable, we are also assuming that the email variable is set beforehand: */ $stmt->execute($email);
Looking at the examples above, you can see that even though the syntax details are different for each language, they are all fundamentally the same because they all use a “?” as a placeholder for the value that will be passed in later. And they all “prepare” the SQL first and execute later, which is of course the whole point behind prepared statements. A good way to think of a prepared statement is as a template for SQL – because of the fact that it’s not a complete SQL statement since it does not have the values it needs in the placeholder areas.
What exactly happens when SQL is “prepared”?
Prepared SQL is created by calling the respective prepare method in each language, as you can see in the examples above. The prepared SQL template is sent to the DBMS (whether it’s MySQL, DB2, or whatever) with the placeholder values (the “?”) left blank. Then, the DBMS will parse, compile, and perform query optimization on the template. After that, the DBMS will store the result, but it can not execute the result because it, of course, does not have any values to execute with since there is no data in the placeholders/parameters. The SQL is only executed once the respective execute function is called and data is passed in for the parameters.
What are the advantages of using prepared statements?
Prepared statements provide 2 primary benefits. The first is that they provide better performance. Even though a prepared statement can be executed many times, it is is compiled and optimized only once by the database engine. Because of the fact that a prepared statement does not have to be compiled and optimized each and every time the values in the query change, it offers a distinct performance advantage. But, keep in mind that not all query optimization can occur when a prepared statement is compiled. This is because the best query plan may also depend on the specific values of the parameters being passed in. The best query plan may also change over time, because of the fact that the database tables and indices also change over time.
Why are prepared statements so effective against SQL injection?
The second advantage of using prepared statements is that they are the best solution to preventing SQL injection attacks. If you are not familiar with SQL injection, it’s highly recommended that you read our article on SQL injection – every programmer should know what SQL injection is. A short, non-academic description of SQL injection is this: any time an application runs SQL based on some user input through a web form, then a hacker could potentially pass in some input with the intent of having his input run as part of your SQL, and either steal or corrupt your users’ data.
Now, back to our discussion: the reason that prepared statements help so much in preventing SQL injection is because of the fact that the values that will be inserted into a SQL query are sent to the SQL server after the actual query is sent to the server. In other words, the data input by a potential hacker is sent separately from the prepared query statement. This means that there is absolutely no way that the data input by a hacker can be interpreted as SQL, and there’s no way that the hacker could run his own SQL on your application. Any input that comes in is only interpreted as data, and can not be interpreted as part of your own application’s SQL code – which is exactly why prepared statements prevent SQL injection attacks.