DFP-300×250-1

Newsletter Subscription

Jobboard

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.

474716 10136Greetings! Quick question thats completely off subject. Do you know how to make your web site mobile friendly? My weblog looks weird when viewing from my iphone. Im trying to locate a template or plugin that may well be able to fix this difficulty. Should you have any recommendations, please share. Appreciate it! 300250

166418 386780Today, I went to the beach with my kids. I found a sea shell and gave it to my 4 year old daughter and said “You can hear the ocean if you put this to your ear.” She put the shell to her ear and screamed. There was a hermit crab inside and it pinched her ear. She never wants to go back! LoL I know this is completely off topic but I had to tell someone! 47088

709366 342694Hello there! I could have sworn Ive been to this weblog before but soon after checking by way of some with the post I realized it is new to me. Anyhow, Im surely glad I identified it and Ill be bookmarking and checking back regularly! 833490

877575 684575We supply you with a table of all of the emoticons that can be used on this application, and the meaning of each symbol. Though it may well take some initial effort on your part, the skills garnered from regular and strategic use of social media will create a strong foundation to grow your business on ALL levels. 724317

669906 636625Following study numerous the websites on your own internet website now, i truly like your means of blogging. I bookmarked it to my bookmark web site list and will also be checking back soon. Pls consider my web-site likewise and tell me what you consider. 492332

38602 805910The vacation trades offered are evaluated a variety of within the chosen and just excellent value all about the world. Those hostels are normally based towards households which you will locate accented by way of charming shores promoting crystal-clear fishing holes, concurrent of ones Ocean. Hotels Discounts 545824

699324 587398Up to now, you need to term of hire an absolute truck or van and will also be removal equipments to valuable items plus take a look at the new destination. From the long run, which end up with are few issues except anxiety moreover stress and anxiety. removals stockport 342773

974999 146762Delighted for you to discovered this internet site write-up, My group is shopping far more often than not regarding this. This can be at this moment surely what I are already seeking and I own book-marked this specific internet site online far too, Ill often be maintain returning soon enough to appear at on your exclusive weblog post. 556635

473605 864438Awesome blog! Is your theme custom made or did you download it from somewhere? A theme like yours with a few simple tweeks would really make my blog stand out. Please let me know where you got your theme. Many thanks 250587

770078 745157We stumbled more than here coming from a different internet page and thought I may well check points out. I like what I see so now im following you. Appear forward to exploring your internet page but once more. 249629

532944 837997Following study a handful of the content in your internet website now, and that i genuinely such as your method of blogging. I bookmarked it to my bookmark web website list and are checking back soon. Pls look into my website as nicely and tell me what you believe. 50023

614354 348683Hi. Cool article. Theres an issue with your website in firefox, and you might want to check this The browser is the market chief and a good section of people will pass over your great writing because of this problem. 90361

112037 111304Ive been absent for some time, but now I remember why I used to enjoy this weblog. Thank you, I will try and check back a lot more often. How often you update your internet web site? 245823

571363 836991Empathetic for your monstrous inspect, in addition Im just seriously very good as an alternative to Zune, and consequently optimism them, together with the quite very good critical reviews some other players have documented, will let you determine whether it does not take appropriate choice for you. 757239

Yalin Meric, Logica IT

I use this helper function on submitted text values to prevent SQL injection. It can be extended to support any database engine. Does anyone see a problem with this solution?: