Why Prepare SQL Queries??

Discussion in 'MySQL' started by pradeep, Jan 2, 2008.

  1. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,645
    Likes Received:
    87
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    http://blog.pradeep.net.in
    Many of the more mature databases like MySQL, Oracle, PostgreSQL, etc. support the concept of prepared statements. Many languages or their database abstraction classes/modules support or sometimes need you to prepare the queries before they are executed, also some modules require that you prepare your query so that you can access the resultset. Here's a Perl example using DBI.

    Code:
     use DBI; ## load the DBI module
     my $dbh = DBI->connect('DBI:mysql:host=localhost:database=myDb','user','pass'); # connect
     
     my $stmt = $dbh->prepare('SELECT * FROM users ORDER BY name');
     $stmt->execute();
     
     while(my @row = $stmt->fetchrow_array())
     {
         # do something with the resultset rows
     }
     
     ## we can also do this without a prepare, where no resultset is available
     $dbh->do('SELECT * FROM users ORDER BY name'); # Usually used for INSERT,UPDATE,DELETE,TRUNCATE
     

    What Are Prepared Queries?



    You can think of them as a kind of compiled template for the SQL that you want to run, that can be customized using variable parameters. Prepared statements offer two major benefits:

    The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. When the query is prepared, the database will analyze, compile and optimize it's plan for executing the query. For complex queries this process can take up enough time that it will noticeably slow down your application if you need to repeat the same query many times with different parameters. By using a prepared statement you avoid repeating the analyze/compile/optimize cycle. In short, prepared statements use fewer resources and thus run faster.

    The parameters to prepared statements don't need to be quoted; the driver handles it for you. If your application exclusively uses prepared statements, you can be sure that no SQL injection will occur. Prepared statements can help increase security by separating SQL logic from the data being supplied. This separation of logic and data can help prevent a very common type of vulnerability called an SQL injection attack. Normally when you are dealing with an ad hoc query, you need to be very careful when handling the data that you received from the user. This entails using functions that escape all of the necessary trouble characters, such as the single quote, double quote, and backslash characters. This is unnecessary when dealing with prepared statements. The separation of the data allows MySQL to automatically take into account these characters and they do not need to be escaped using any special function.

    When Should You Use Prepared Statements?



    Prepared statements can be useful for all of the above reasons, however they should not (and can not) be used for everything in your application. First off, the type of queries that they work on is limited to DML (INSERT, REPLACE, UPDATE, and DELETE), CREATE TABLE, and SELECT queries. Support for additional query types will be added in further versions, to make the prepared statements API more general.

    Sometimes prepared statements can actually be slower than regular queries. The reason for this is that there are two round-trips to the server, which can slow down simple queries that are only executed a single time. In cases like that, one has to decide if it is worth trading off the performance impact of this extra round-trip in order to gain the security benefits of using prepared statements.

    Common Mistakes



    Many novice programmers do not know the importance of prepared statements, so they sometimes end up misusing it. Here's an example where you have an array of values which need to be inserted into a table 'sales'.

    Code:
     use DBI; ## load the DBI module
     my $dbh = DBI->connect('DBI:mysql:host=localhost:database=myDb,user,pass'); # connect
     
     my @salesData = ('PKU001,12,32.00', ## product_code, quantity, price
              'PKU013,156,22.00',
              'PKU043,16,34.00',
              'PKU003,100,38.00',
              'PKU033,13,66.00');
     
     ## The common mistake
     foreach (@salesData)
     {
         my ($product_code, $qty, $price) = split /,/$_;
         my $sql = $dbh->prepare("INSERT INTO sales (product_code, qty, price) VALUES ('$product_code', '$qty', '$price')");
         $sql->execute();
     }
     
     ## In this case the query has to prepared everytime and then execute, adds a prepare overhead for each row
     ## The correct way
     my $sth = $dbh->prepare(q{INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?)}) or die $dbh->errstr;
     foreach (@salesData)
     {
         my ($product_code, $qty, $price) = split /,/;
         $sth->execute($product_code, $qty, $price) or die $dbh->errstr;
     }
     
    Without using placeholders, the insert statement shown previously would have to contain the literal values to be inserted and would have to be re-prepared and re-executed for each row. With placeholders, the insert statement only needs to be prepared once. The bind values for each row can be given to the execute method each time it's called. By avoiding the need to re-prepare the statement for each row, the application typically runs many times faster.

    Final thoughts



    Now you should have a good idea about the advantages and disadvantages of when to use prepared statements. They can lead to faster, more secure code if used properly; they work quite well as a complement to the current APIs that are currently available.

    Further Reading



    http://in.php.net/pdo - PHP : PDO
    http://www-128.ibm.com/developerworks/db2/zones/informix/library/techarticle/db_query_dispatch.html - How Queries Execute
    http://www.petefreitag.com/item/390.cfm - MySQL Query Cache
     
  2. aisha.ansari84

    aisha.ansari84 New Member

    Joined:
    Feb 13, 2008
    Messages:
    82
    Likes Received:
    1
    Trophy Points:
    0
  3. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    Its not a duplicate.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice