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.

Hiring? Job Hunting? Post a JOB or your RESUME on our JOB BOARD >>

Subscribe to our newsletter for more free interview questions.

  • 148627 606203Hi there! I just wish to give an enormous thumbs up for the good information youve appropriate here on this post. I shall be coming once again to your weblog for extra soon. 580699

  • 594257 931751I preferred than you might be now. 801664

  • 389816 93930Keep up the great work , I read few blog posts on this site and I believe that your website is real interesting and has bands of good info . 405496

  • 934421 972091Any way Im going to be subscribing for a feed and I hope you write-up once more soon 219910

  • 471698 52907You should take part in a contest for one of the best blogs on the web. I will recommend this site! 924435

  • 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

  • 556062 295333This really is a wonderful internet page, could you be interested in performing an interview about just how you created it? If so e-mail me! 211507

  • 28944 342304A great deal of writers recommend just writing and composing no matter how bad and if the story is going to develop, you will suddenly hit the zone and itll develop. 758882

  • 457553 977876Hey, you used to write wonderful, but the last few posts have been kinda boringK I miss your super writings. Past few posts are just a little out of track! come on! 810019

  • 99976 167917You ought to join in a contest very first with the greatest blogs on the internet. I will recommend this internet web site! 293600

  • 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

  • 628893 621761Really superb details can be located on weblog . 795226

  • 992435 442517Thank you for your style connected with motive though this data is certain spot a new damper within the sale with tinfoil hats. 158592

  • 607123 148648This really is some excellent details. I expect additional facts like this was distributed across the internet today. 316892

  • 18235 554701I like this site really significantly so significantly superb details. 212522

  • 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

  • 367260 498544Thank you for the auspicious writeup. It in reality was a amusement account it. Look complicated to far delivered agreeable from you! Nevertheless, how can we keep in touch? 795433

  • 309841 227827What may you suggest in regards to your post that you made a few days ago? Any sure? 384389

  • 352163 112191An incredibly interesting examine, I may possibly not concur entirely, but you do make some very valid points. 803829

  • 675809 351443This is such a great post, and was thinking much the same myself. Another great update. 510820

  • 689792 712270Visit our site for information about securities based lending and more. There is information about stock and equity loans as well as application forms. 95288

  • 123467 109970baby strollers with high traction rollers should be much safer to use compared to those with plastic wheels- 527600

  • 922269 432503Billiard is really a game which is mostly played by the high class individuals 21891

  • 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

  • 348907 552720Thanks for the post, was an interesting read. Curious as to how you came about that solution 495117

  • 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

  • 583670 462287You got a extremely outstanding web site, Glad I noticed it via yahoo. 652755

  • 323527 932477Some truly amazing articles on this internet site , appreciate it for contribution. 839883

  • 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

  • 306850 440853You appear to be quite specialist inside the way you write.::~ 846542

  • 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

  • 194924 127083Really fascinating information !Perfect just what I was looking for! 670140

  • 784524 419730Fantastic web site you got here! Yoo man fantastic reads, post some much more! Im gon come back so much better have updated 314950

  • 999576 341539This is an outstanding write-up and I completely comprehend where your coming from within the third section. Perfect read, Ill regularly follow the other reads. 589550

  • 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

  • 525560 284770I actually enjoyed reading this web site, this really is excellent weblog. 928683

  • 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

  • 922863 502231So, is this just for men, just for women, or is it for both sexes If it s not, then do women need to do anything different to put on muscle 180451

  • 131654 56889cleaning supplies need to have earth friendly organic ingredients so that they do not harm the environment 644176

  • 725055 203755I think one of your ads triggered my internet browser to resize, you might want to put that on your blacklist. 125616

  • 722631 830403This site is my inhalation, actually wonderful layout and Perfect written content material. 663173

  • 460566 453914some truly fascinating information , properly written and broadly speaking user genial . 571523

  • BBB

    767026 591714Thanks for helping out, superb info . 183946

  • 574150 704361Dude. You mind if I link to this post from my own web site? This really is just too awesome. 974543

  • 301776 173869I was suggested this web website by my cousin. Im not certain whether this post is written by him as nobody else know such detailed about my issue. Youre incredible! Thanks! 603194

  • 213075 863863quite very good publish, i in fact really like this internet internet site, carry on it 888716

  • BBB

    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?:

    ///

    /// Make Valid Text Usable In SQL Command

    ///

    /// Database Type

    /// Text Value

    /// Corrected text value

    ///

    public static string MakeValidTextUsableInSQLCommand(DatabaseType DBType, string TextValue)

    {

    if (TextValue.Length > 0)

    {

    int iCounter;

    string cCharacter;

    byte[] aCode;

    byte iCode;

    string mResult = “”;

    System.Text.UTF8Encoding obEncode = new System.Text.UTF8Encoding();

    for (iCounter = 0; iCounter 64) & (iCode 96) & (iCode 47) & (iCode 128))

    {

    mResult += cCharacter;

    }

    else

    {

    switch (DBType)

    {

    case DatabaseType.Access:

    mResult += “‘ + CHR(” + iCode + “) + ‘”;

    break;

    case DatabaseType.MSSQL:

    mResult += “‘ + CHAR(” + iCode + “) + ‘”;

    break;

    }

    }

    }

    return mResult;

    }

    else

    {

    return “”;

    }

    }

  • shutear

    //Search as email or phoneNum
    String sql = “select * from tableName”;
    List paramsList = new ArrayList();

    if(email != null)
    {
    sql += ” where email = ?”;
    paramsList.add(email);
    }
    else if(phoneNum != null)
    {
    sql += ” where phoneNum = ?”;
    paramsList.add(phoneNum);
    }
    else
    {
    //no condition,search all items
    }

    PreparedStatement stmt = connection.prepareStatement(sql);

    int paramsNum = paramsList.size();
    for(int i=0;i < paramsNum;i++)
    {
    stmt.setObject(i+1,paramsList.get(i));
    }

    ResultSet result = stmt.executeQuery();

  • Nitin

    What if the query being prepared depends on a condition.

    If(mycondition){
    sqlExpr.append("TBCUSTOMER = '").append(myCustId).append("'");
    }

    Where condition is altered by checking some condition inside the block. How it is supposed to be handled while using PreparedStatement