Are There Downsides to Using Prepared Statements

Are there downsides to using prepared statements?

Prepared statement is just a parsed and precompiled SQL statement which just waits for the bound variables to be provided to be executed.

Any executed statement becomes prepared sooner or later (it need to be parsed, optimized, compiled and then executed).

A prepared statement just reuses the results of parsing, optimization and compilation.

Usually database systems use some kind of optimization to save some time on query preparation even if you don't use prepared queries yourself.

Oracle, for instance, when parsing a query first checks the library cache, and if the same statement had already been parsed, it uses the cached execution plan instead.

Are there downsides to having many SQLite prepared statements at once?

Prepared statements do not need much memory.
While optimizing away the SQL parsing overhead is probably not worth the effort, it will not hurt.

When *not* to use prepared statements?

I think you want PDO::ATTR_EMULATE_PREPARES. That turns off native database prepared statements, but still allows query bindings to prevent sql injection and keep your sql tidy. From what I understand, PDO::MYSQL_ATTR_DIRECT_QUERY turns off query bindings completely.

When is it better to use a Statement over a PreparedStatement?

In most real-life case, there is rarely case that createStatement is better than preparedStatement.

I believe almost everyone are aware of benefit of using prepared statement, just to name a few:

  • Less vulnerable to sql injection
  • better performance as to avoid SQL parsing same SQL with different parameter
  • etc

Most of these benefits comes from reusing the same statement, and setting parameters separately (instead of embedding in the query itself) when you are using prepared statement.

The only benefit (that I am aware of) by using createStatement() is you can use same statement object to execute different SQLs, while when using prepared statement, you will need to create PrepredStatement for each query.

In real life this is seldom meaningful. However, when you are developing an application that will allow user to input arbitrary query (which means you cannot set parameter separately), then there is no obvious benefit of using prepared statement. And then, if such application is going to query a lot of times using different query string (for example, you are building a SQL client), then doing createStatement once, and reuse the Statement object to execute different queries may gain you some (marginal) performance gain with less object allocation.

Should I always prefer working with prepared SQL statements, for performance benefits?

Prepared Statements:

Why use prepared statements?

There are numerous advantages to using
prepared statements in your
applications, both for security and
performance reasons.

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.

The increase in performance in
prepared statements can come from a
few different features. First is the
need to only parse the query a single
time. When you initially prepare the
statement, MySQL will parse the
statement to check the syntax and set
up the query to be run. Then if you
execute the query many times, it will
no longer have that overhead. This
pre-parsing can lead to a speed
increase if you need to run the same
query many times, such as when doing
many INSERT statements.

(Note: While it will not happen with
MySQL 4.1, future versions will also
cache the execution plan for prepared
statements, eliminating another bit of
overhead you currently pay for each
query execution.)

The second place where performance may
increase is through the use of the new
binary protocol that prepared
statements can use. The traditional
protocol in MySQL always converts
everything into strings before sending
them across the network. This means
that the client converts the data into
strings, which are often larger than
the original data, sends it over the
network (or other transport) to the
server, which finally decodes the
string into the correct datatype. The
binary protocol removes this
conversion overhead. All types are
sent in a native binary form, which
saves the conversion CPU usage, and
can also cut down on network usage.

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.

Advantages of using prepared statements over normal mysqli statements?

Escaping bad characters is still needed, but the library does it automatically for all parameters you bind. It's just slightly more convenient, and prevents the programmer from forgetting to sanitize a value.

However, note that this automatism is limited to parameters!

The following query is safe, because bind_param() takes care of escaping:

$code = $_GET["code"];
$name= $_GET["name"];
$percentage= $_GET["percentage"];

$stmt = $mysqli->prepare("INSERT INTO items VALUES (?, ?, ?)");
$stmt->bind_param('iss', code, $name, $percentage);
$stmt->execute();

the following query is unsafe, because anything you put directly into the query will not be escaped automatically:

$tablename = $_GET["prefix"]."_items";  
$code = $_GET["code"];
$name= $_GET["name"];
$percentage= $_GET["percentage"];

---- UNSAFE! ----
$stmt = $mysqli->prepare("INSERT INTO `$tablename` VALUES (?, ?, ?)");
$stmt->bind_param('iss', $code, $name, $percentage);
$stmt->execute();

that said, one shouldn't be using dynamic table names like shown in this example anyway. But the point stands: Be careful, even with parametrized queries!

The only downside I can think of is that you can't see the final query any more for debugging (because it gets assembled only on server side).

prepared statements - are they necessary

Prepared statements offer excellent protection against SQL injection.

In addition to SQL injection protection, prepared statements offer reduced load on the database server when the same query is to executed multiple times, such as in an INSERT loop. The statement is only compiled once by the RDBMS rather than needing to be compiled each time as it would in a mysql_query() call.

Different APIs require varying amounts of code to execute a prepared statement. I find that PDO can be a little less verbose than MySQLi, if for example your situation permits the use of implicit parameter binding inside the execute() call. This only works, if all your params can be evaluated as strings though.

// PDO implicit binding example:
// Not many lines of code if the situation allows for it
$stmt = $pdo->prepare("SELECT * FROM tbl WHERE col1=? AND col2=? AND col3=?");
$stmt->execute(array($val1, $val2, $val3));


Related Topics



Leave a reply



Submit