Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/articles/mysql/)
-   -   Why Prepare SQL Queries?? (http://www.go4expert.com/articles/prepare-sql-queries-t8074/)

pradeep 2Jan2008 12:03

Why Prepare SQL Queries??
 
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: Perl

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

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/developerwork..._dispatch.html - How Queries Execute
http://www.petefreitag.com/item/390.cfm - MySQL Query Cache

aisha.ansari84 20Feb2008 18:03

Re: Why Prepare SQL Queries??
 
copied from
http://dev.mysql.com/tech-resources/...tatements.html

shabbir 20Feb2008 21:45

Re: Why Prepare SQL Queries??
 
Its not a duplicate.


All times are GMT +5.5. The time now is 04:07.