33

As far as I understand, prepared statements are (mainly) a database feature that allows you to separate parameters from the code that uses such parameters. Example:

PREPARE fooplan (int, text, bool, numeric) AS
    INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);

A parameterized query substitutes the manual string interpolation, so instead of doing

cursor.execute("SELECT FROM tablename WHERE fieldname = %s" % value)

we can do

cursor.execute("SELECT FROM tablename WHERE fieldname = %s", [value])

Now, it seems that prepared statements are, for the most part, used in the database language and parameterized queries are mainly used in the programming language connecting to the database, although I have seen exceptions to this rule.

The problem is that asking about the difference between prepared statement and parameterized query brings a lot of confusion. Their purpose is admittedly the same, but their methodology seems distinct. Yet, there are sources indicating that both are the same. MySQLdb and Psycopg2 seem to support parameterized queries but don’t support prepared statements (e.g. here for MySQLdb and in the TODO list for postgres drivers or this answer in the sqlalchemy group). Actually, there is a gist implementing a psycopg2 cursor supporting prepared statements and a minimal explanation about it. There is also a suggestion of subclassing the cursor object in psycopg2 to provide the prepared statement manually.

I would like to get an authoritative answer to the following questions:

  • Is there a meaningful difference between prepared statement and parameterized query? Does this matter in practice? If you use parameterized queries, do you need to worry about prepared statements?

  • If there is a difference, what is the current status of prepared statements in the Python ecosystem? Which database adapters support prepared statements?

Community
  • 1
  • 1
r_31415
  • 8,752
  • 17
  • 74
  • 121
  • In Python, I think a "parameterized" query is a feature of cursor objects that is generally implemented using prepared statements. Sometimes the two are used interchangeably when writing SQL code, depending on which aspect is being emphasized (putting parameters in statements or caching the query plan). – Gordon Linoff Apr 02 '16 at 00:03
  • Thanks. Maybe parameterized implies prepared statement, but then why so many people arguing about prepared statements not being supported by their database adapters (e.g. [this comment](http://stackoverflow.com/questions/1947750/does-python-support-mysql-prepared-statements#comment30763896_1947993) referencing [this project](https://pythonhosted.org/oursql/api.html?highlight=prepared#oursql.Connection.cursor)? – r_31415 Apr 02 '16 at 00:50

4 Answers4

32

  • Prepared statement: A reference to a pre-interpreted query routine on the database, ready to accept parameters

  • Parametrized query: A query made by your code in such a way that you are passing values in alongside some SQL that has placeholder values, usually ? or %s or something of that flavor.

The confusion here seems to stem from the (apparent) lack of distinction between the ability to directly get a prepared statement object and the ability to pass values into a 'parametrized query' method that acts very much like one... because it is one, or at least makes one for you.

For example: the C interface of the SQLite3 library has a lot of tools for working with prepared statement objects, but the Python api makes almost no mention of them. You can't prepare a statement and use it multiple times whenever you want. Instead, you can use sqlite3.executemany(sql, params) which takes the SQL code, creates a prepared statement internally, then uses that statement in a loop to process each of your parameter tuples in the iterable you gave.

Many other SQL libraries in Python behave the same way. Working with prepared statement objects can be a real pain, and can lead to ambiguity, and in a language like Python which has such a lean towards clarity and ease over raw execution speed they aren't really the greatest option. Essentially, if you find yourself having to make hundreds of thousands or millions of calls to a complex SQL query that gets re-interpreted every time, you should probably be doing things differently. Regardless, sometimes people wish they could have direct access to these objects because if you keep the same prepared statement around the database server won't have to keep interpreting the same SQL code over and over; most of the time this will be approaching the problem from the wrong direction and you will get much greater savings elsewhere or by restructuring your code.*

Perhaps more importantly in general is the way that prepared statements and parametrized queries keep your data sanitary and separate from your SQL code. This is vastly preferable to string formatting! You should think of parametrized queries and prepared statements, in one form or another, as the only way to pass variable data from your application into the database. If you try to build the SQL statement otherwise, it will not only run significantly slower but you will be vulnerable to other problems.

*e.g., by producing the data that is to be fed into the DB in a generator function then using executemany() to insert it all at once from the generator, rather than calling execute() each time you loop.

tl;dr

A parametrized query is a single operation which generates a prepared statement internally, then passes in your parameters and executes.

edit: A lot of people see this answer! I want to also clarify that many database engines also have concepts of a prepared statement that can be constructed explicitly with plaintext query syntax, then reused over the lifetime of a client's session (in postgres for example). Sometimes you have control over whether the query plan is cached to save even more time. Some frameworks use these automatically (I've seen rails' ORM do it aggressively), sometimes usefully and sometimes to their detriment when there are permutations of form for the queries being prepared.

Also if you want to nit pick, parametrized queries do not always use a prepared statement under the hood; they should do so if possible, but sometimes it's just formatting in the parameter values. The real difference between 'prepared statement' and 'parametrized query' here is really just the shape of the API you use.

Community
  • 1
  • 1
Mumbleskates
  • 1,248
  • 10
  • 18
  • 5
    *A parametrized query is a single operation which generates a prepared statement internally, then passes in your parameters and executes.*. Only if the database adapter actually implements it like this. The popular MySQL adapters still use string interpolation to create the final query with data to send to the database, for example. They do a good job of sanitising the data first.. – Martijn Pieters Apr 10 '16 at 09:36
  • @Widdershins Thank you for your response. – r_31415 Apr 11 '16 at 00:46
  • @MartijnPieters That's pretty much my main concern. Most adapters (including postgreSQL adapters) don't seem to include prepared statements support. If all they do is sanitizing but not prepared statements, I wonder how much risk of SQL injection remains. – r_31415 Apr 11 '16 at 00:48
  • 2
    @RobertSmith: There isn't, not with the long-maintained modules. Sanitising isn't *that* hard, but it is still best left to the driver at the very least. There is no more or less risk than using prepared statements, because the database can't distinguish between an embedded literal in the statement and the programmer having interpolated un-sanitised input. – Martijn Pieters Apr 11 '16 at 06:29
  • @RobertSmith: There isn't, not with the long-maintained modules. Sanitising isn't *that* hard, but it is still best left to the driver at the very least. There is no more or less risk than using prepared statements, because the database can't distinguish between an embedded literal in the statement and the programmer having interpolated un-sanitised input. – Martijn Pieters Apr 11 '16 at 06:29
  • @Martijn Pieters you do raise a good point. There is no guarantee that a library will rely on the database driver's sanitizer. Perhaps the driver doesn't even support storing parameterized queries; with an implementation as poor as MySQL, I wouldn't make bets. While this isn't typically dangerous for the developer using the library, there is another point of risk in assuming that the maker of the library implemented their sanitizer correctly. – Mumbleskates Apr 11 '16 at 15:13
  • Also @Robert Smith, remember that there is a difference between a library that has provisions for exposing prepared statements to the user and one that doesn't use prepared statements at all. The difference in risk when inserting data is negligible, and in either case orders of magnitude less likely to cause issues than implementing a sanitizer yourself. – Mumbleskates Apr 11 '16 at 15:18
  • @MartijnPieters Well, if the database adapter is only sanitizing the user input but without bound parameters, there is a real risk of sql injection. I would feel much more comfortable with an application using bound parameters even without prepared statements in the database. – r_31415 Apr 11 '16 at 23:51
  • @RobertSmith It's not really possible to sanitize input that has already been constructed by another process, such as the user interpolating a string. That would imply divining the true intent of the user, the oldest problem in computing. When we're talking about the adapter sanitizing the input here, we're talking about it taking parameters and doing the interpolation and sanitizing itself, rather than leaving this task to the underlying driver (assuming said driver even has parametrized statements, as mentioned). – Mumbleskates Apr 12 '16 at 00:13
  • @Widdershins Thank you. Then, to be clear, when the adapter (e.g. psycopg2) is 'sanitizing' , it is also using bound parameters to construct the SQL query? – r_31415 Apr 12 '16 at 00:42
  • @RobertSmith I'm actually not completely positive. I'm pretty sure it does, and it should be: when they provide `executemany()`, one would hope and assume that it is doing this efficiently in the backend, by building a prepared statement rather than making the server re-parse everything thousands of times. I would have to check the docs. I know that sqlite3 uses prepared statements internally, and the [psycopg2 docs](http://initd.org/psycopg/docs/cursor.html#cursor.executemany) say that it 'prepares and executes' so one would assume that is the case. – Mumbleskates Apr 12 '16 at 18:57
  • @RobertSmith In fact as far as I know, the underlying postgresql bindings may have a total separation between the preparation and execution of statements, which is a good way to do it. – Mumbleskates Apr 12 '16 at 18:59
  • @Widdershins I think there was another SO question related to that statement 'prepares and executes', but I remember the question was referring to prepared statements in the database (which is not supported by most adapters). However, one would hope there is an equivalent preparation in the adapter. Probably I will ask, at least in the case of python libraries. – r_31415 Apr 12 '16 at 19:12
6

First, your questions shows very good preparation - well done.

I am not sure, if I am the person to provide authoritative answer, but I will try to explain my understanding of the situation.

Prepared statement is an object, created on side of database server as a result of PREPARE statement, turning provided SQL statement into sort of temporary procedure with parameters. Prepared statement has lifetime of current database session and are discarded after the session is over. SQL statement DEALOCATE allows destroying the prepared statement explicitly.

Database clients can use SQL statement EXECUTE to execute the prepared statement by calling it's name and parameters.

Parametrized statement is alias for prepared statement as usually, the prepared statement has some parameters.

Parametrized query seems to be less often used alias for the same (24 mil Google hits for parametrized statement, 14 mil hits for parametrized query). It is possible, that some people use this term for another purpose.

Advantages of prepared statements are:

  • faster execution of actual prepared statement call (not counting the time for PREPARE)
  • resistency to SQL injection attack

Players in executing SQL query

Real application will probably have following participants:

  • application code
  • ORM package (e.g. sqlalchemy)
  • database driver
  • database server

From application point of view it is not easy to know, if the code will really use prepared statement on database server or not as any of participants may lack support of prepared statements.

Conclusions

In application code prevent direct shaping of SQL query as it is prone to SQL injection attack. For this reason it is recommended using whatever the ORM provides to parametrized query even if it does not result on using prepared statements on database server side as the ORM code can be optimized to prevent this sort of attack.

Decide, if prepared statement is worth for performance reasons. If you have simple SQL query, which is executed only few times, it will not help, sometime it will even slow down the execution a bit.

For complex query being executed many times and having relatively short execution time will be the effect the biggest. In such a case, you may follow these steps:

  • check, that the database you are going to use supports the PREPARE statement. In most cases it will be present.
  • check, that the drive you use is supporting prepared statements and if not, try to find another one supporting it.
  • Check support of this feature on ORM package level. Sometime it vary driver by driver (e.g. sqlalchemy states some limitations on prepared statements with MySQL due to how MySQL manages that).

If you are in search for real authoritative answer, I would head to authors of sqlalchemy.

Jan Vlcinsky
  • 42,725
  • 12
  • 101
  • 98
  • Thank you! I think you are making the right distinctions here. Prepared statements and parameterized statement/query are the same but applied differently throughout the application. As far as I understand, most database drivers in Python don't support prepared statements on the database (the exception being oursql) and my concerns are primarily focused on security (not so much performance.) I guess that there is some security risk involved in not supporting prepared statements on the database. I would be very interested in knowing to what extent this is true. – r_31415 Apr 09 '16 at 00:16
0

An sql statement can't be execute immediately: the DBMS must interpret them before the execution.

Prepared statements are statement already interpreted, the DBMS change parameters and the query starts immediately. This is a feature of certain DBMS and you can achieve fast response (comparable with stored procedures).

Parametrized statement are just a way you compose the query string in your programming languages. Since it doesn't matter how sql string are formed, you have slower response by DBMS.

If you measure time executing 3-4 time the same query (select with different conditions) you will see the same time with parametrized queries, the time is smaller from the second execution of prepared statement (the first time the DBMS has to interpret the script anyway).

Fil
  • 1,032
  • 13
  • 29
  • 6
    hmmm, actually, the part about parametrized being slow is not totally true. many databases will automatically cache an execution plan and will use re-use it on subsequent passes, if the only changing thing is the bind parameters. another reason, besides sql injection concerns, to avoid rolling your own query by injecting variables directly into the query string. – JL Peyret Apr 02 '16 at 00:12
  • Thank you for your answer. I guess you're indirectly claiming that prepared statements and parameterized queries are not the same and that speed is a meaningful difference. – r_31415 Apr 02 '16 at 00:55
0

I think the comment about using executemany fails to deal with the case where an application stores data into the database OVER TIME and wants each insert statement to be as efficient as possible. Thus the desire to prepare the insert statement once and re-use the prepared statement. Alternatively, one could put the desired statement(s) in a stored proc and re-use it.

Jay
  • 51
  • 3