19 Sep 2014

Prepared Statements: Performance, Security, Tips

NB: in this article we talk mostly about PreparedStatement, but most of the information is suitable for CallableStatement as well.
A lot of us know that JDBC can operate with different kinds of Statements including simple Statement, PreparedStatement and CallableStatement. Let’s talk about their purpose and nuances.

First of all it’s worth mentioning that when databases receive an SQL query they check its syntax, parse it (soft parse), optimise it (hard parse) and create a query plan for it. Query Plan is a DB-level instruction on how exactly it’s going to execute the query.

DBs can cache queries they execute, thus if we’re doing something like this: select * from books and then invoke it several times, then at some point database will start caching it. In most systems a cache is just a map, which means it has a key and a value. In our case the key is the query (well, its hash) and the value is the Query Plan. When DB receives next identical query, it checks whether there is a Query Plan already prepared waiting to be fetched from the cache. If it’s there, then database won’t need to parse it again and we’ll get a performance boost (though it still re-builds the Query Plan in some cases when it wants to optimise the query even more).

It’s worth noticing one more time that the key in the cache map is a query. This means that this SQL: select * from books where id=1 and this one: select * from books where id=2 are different, they both will be compiled. Thus we can’t cache the same query just because it has different parameters! Well, that’s exactly the case PreparedStatement can cope with. It’s possible to rewrite the query to: select * from books where id=?. Then each time we want to execute it, database will take the Query Plan from the cache, the only thing left is just to pass parameters which will replace the question mark. Of course every database wants to be the best and it works with cache in its own way: some of DBs might invalidate cache entries quickly, others will wait longer, the decision may depend on statistics after all.
Now what happens when we use JDBC:
1. We start with connection.prepareStatement("some query"), JDBC Driver asks database to prepare the statement*. DB answers with the identifier of the query (most probably - its hash) and additional data like number of params in the query.
2. Next we invoke executeQuery() and JDBC Driver sends the identifier of the query and params. Database finds the query by its ID and simply uses its Query Plan without the need of parsing the query again.

Some of you may notice, that next time we invoke connection.prepareStatement() there will be another communication to the database in order to prepare the query, and then yet another communication for actual query execution. First of all there are 2 extra network requests which is bad from performance point. Second, why the heck do we prepare it again while we already have query ID? Wouldn’t it be simpler to reuse the same PreparedStatement? Well, that’s exactly what happens under the hood!

Connection#prepareStatement(String sql) - this is where magic takes place. JDBC Driver checks the object in its internal cache (not a DB cache!) by the query we passed to the method. And if it’s there, then old PreparedStatement is returned. If not, a new object is created**. This is called implicit caching***.

Next stop: PrepareStatement#close() - this method doesn’t quite stand for its name, it doesn’t actually close the statement but rather places it to the internal cache. Physically PreparedStatment gets closed only in cases a) if Connection is closed b) when cache reaches its max capacity and we need to empty it from old and rarely-used statements c) if cache is switched off d) if cache is not supported by JDBC Driver.

Finally, here are some points related to MySQL (well, most of the stuff will still be common to other databases):

  • SQLs have to be identical (queries with words USERS and users are different!) - that’s true for all the databases****

  • PreparedStatements are not always cached the first time they’re executed, sometimes you need to query database multiple times.

  • Connections to different MySQL Servers, or Connections using different protocols, or even two Connections with different encodings - they all will use different caches.

  • Query shouldn’t start with spaces (well, to be true I’m not quite sure on this, but I’m tired of reading docs already :) For PostgreSQL this is true).

  • Sub-queries and queries with UNION are not cached.

  • Queries inside stored procedures are not cached.

  • MySQL Server < 5.1.17 doesn’t cache the queries, higher versions have their own “ester eggs” which sometimes do not allow caching the queries, so read docs carefully!

  • You should set cachePrepStmts to true, it’s switched off by default. Use connection params like prepStmtCacheSize and prepStmtCacheSqlLimit for MySQL configuration.

Security benefits of Prepared Statements?

Besides performance-related features PreparedStatements secures us from SQL Injections. In order to make explanation short, the example will be silly. Let’s say we have a forum engine and functionality “Remove User”. We specify the username on UI and Submit the form. On the back end we have a code like this:

String query = "delete from users where username=" + username;`

You should remember for your entire life - this is nasty! If a bad guy would pass on UI something like this: smith ' or 'a'='a then we’ll get such a query: delete from users where username='smith' or 'a'='a'. Because a always equals to a our where statement will always be true for all the records in the table. And all of them will be deleted. In order to be safe in this case we would need to escape the string. This means that all the symbols that are meaningful for the database (like quote symbol) should be replaced with some other char sequence. If you do it yourself, it would look like this:
delete from users where username='smith\' or \'a\'=\'a'

Because all the quotes are replaced with \’ which means ‘interpret the quote as a string, not as a command’, we’re safe now. But bad guys have a large arsenal, they will always win unless you have a bullet-proof solution. PreparedStatement is is this solution. Because in case of PreparedStatements queries look like this: delete from users where username=? and we don’t construct queries with parameters ourselves: preparedStatement.setString(1, username), we’re protected from any kind of SQL Injections - all the escaping is managed by the database itself.


* JDBC Drivers that don’t support pre-compilation (Prepared Statements) send queries only on the executeQuery() step.
** Notice that when we create usual Statement we don’t pass strings which means every time a new object is instantiated.
*** Actually some JDBC Drivers (like Oracle) can cache usual Statements as well. In case of Oracle JDBC Driver you’d need to work with implementation-specific API and it still won’t be that effective. That’s called Explicit Caching.
**** Of course I didn’t look at every single database, but that’s true for 3 of the most popular drivers I’ve looked into.

Read more
18 Dec 2012

PreparedStatement (подготовленные запросы)

NB: хотя здесь и ведется повествование про PreparedStatement, большая часть оного правдива и для CallableStatement.
Как, наверное, большинству известно в JDBC можно создавать разные виды Statement’ов включая обычные Statement, PreparedStatement и CallableStatement. Здесь мы обсудим что такое PreparedStatement и зачем он нужен.
Итак, начнем с того, что СУБД при поступлении в них запросов, проверяют их синтаксис, разбирают (soft parse), оптимизируют (hard parse) и создают некий query plan – то как на самом деле уже СУБД будет выполнять запрос, какие операции она будет при этом делать.
СУБД умеют кешировать выполненные запросы, то бишь если взять запрос: select * from books. И выполнять его несколько раз, то на каком-то этапе СУБД начнет его кешировать. Кеш во многих системах – это мапа, что значит, что у нее есть ключ и значение. В данном случае в качестве ключа будет SQL запрос (а точнее его хеш), а в качестве значения – разобранный план. Когда СУБД получает очередной запрос с тем же текстом, она проверяет кеш и если там уже есть скомпилированный запрос, то она использует его вместо того, чтоб наново его разбирать (на самом деле периодически СУБД доделывают hard parse, чтоб оптимизировать запрос еще больше).
Заметьте, что в качестве ключа используется полностью тело запроса. Это значит, что запрос:
select * from books where id=1 и запрос select * from books where id=2 – не являются одинаковыми и оба будут компилироваться, что значит что мы не можем кешировать один и тот же запрос только из-за того, что параметры каждый раз разные! Эту проблему решает PreparedStatement, который имеет вид: select * from books where id=? В данном случае СУБД закеширует запрос и будет только лишь подставлять новые параметры вместо знака вопроса. Это позволяет существенно ускорить обработку запросов. Разные СУБД конечно же по-разному реализуют кеш и время, когда он будет задействован (что часто основывается на статистике или просто на указаном пользователем значении).
Что происходит под капотом:

  1. Когда выполняется connection.prepareStatement("some query") драйвер обращается к СУБД для подготовки запроса*, которая возвращает обратно идентификатор запроса (его хеш как правило) и еще некоторые данные, такие как количество параметров в запросе.
  2. При вызове executeQuery() драйвер отсылает лишь идентификатор запроса и параметры, СУБД по ID находит уже разобранный запрос и выполняет его.

Но прозорливый читатель сразу заметит, что при следующем выполнении connection.prepareStatement() снова произойдет сначала вызов prepare к БД, затем собственно вызов для выполнения конкретного запроса. Во-первых, это два вызова по сети, что не хорошо с точки зрения производительности. Во-вторых, хоть СУБД и закешировало запрос и не будет его второй раз разбирать, у нас уже есть ID запроса после первого раза, зачем нам снова лазить в БД за ним, если его можно где-то сохранить и переиспользовать? Собственно так это и реализовано в большинстве драйверов:
Connection#prepareStatement(String sql) – здесь и происходит вся магия, по переданной в метод строке драйвер проверяет объект в своем внутреннем (не СУБД!) кеше, и, если тот там есть, возвращает его; если нет – создает новый.** Это называется неявным кешированием (implicit cache)***.
Идем дальше: PreparedStatement#close() - собственно этот метод и не оправдывает своего имени в данном случае – он не закрывает на самом деле statement, а помещает его как раз таки в кеш. Физически PreparedStatement закрывается только в случае а) если соединение с БД было закрыто б) когда кеш достигает своего максимальной вместимости и нужно освобождать его от старых и малоиспользуемых statement’ов в) если кеш отключен г) если кеш не поддерживается драйвером :)
Но это еще не все. В большистве случаев приложения работают не напрямую с соединениями, создавая и закрывая их, а с пулами соединений (например, DBCP, C3P0), которые сами их создают и предоставляют вашему коду. Так вот, если в обычном случае PreparedStatements привязаны к одному соединению и не могут быть переиспользованы, то пулы позволяют каждому соединению использовать подготовленные запросы других соединений, что означает, что производительность вырастет еще больше.
Опишу пункты, которые нужно знать при работе с подготовленными запросами в MySQL (многое из этого подходит и для других СУБД):

  • Запросы должны точно совпадать (запросы со словами USERS и users - будут считаться разными) - это правда для всех СУБД****

  • Не всегда PreparedStatement кешируются с первого раза, часто их нужно выполнить по несколько раз.

  • Соединения к разным MySQL серверам, или использующие два разных протокола, или даже просто - два одинаковых соединения с разными кодировками по умолчанию, - они все будут использовать разные кеши, которые не будут переиспользоваться другими соедиениями.

  • Запрос не должен начинаться с пробелов (если честно, то не уверен, что это правда для MySQL, но уже сил нет читать документацию :) Для PostgreSQL это так).

  • Подзапросы и запросы с UNION не кешируются.

  • Запросы внутри хранимых процедур не кешируются.

  • MySQL (не драйвер, а сам сервер) до версии 5.1.17 не кеширует запросы, у версий выше есть тоже свои “пасхальные яйца”, из-за который невозможно кешировать запрос, поэтому читайте обязательно документацию.

  • Обязательно установите свойство cachePrepStmts в true, ибо по умолчанию оно отключено! Используйте параметры соединения, такие как prepStmtCacheSize и prepStmtCacheSqlLimit для конфигурации MySQL драйвера.

Какие еще плюшки нам дает PreparedStatement?

Кроме улучшения производительности, подготовленные запросы защищают от SQL Injections. Чтобы было совсем просто понять суть, пример будет очень простым и глупым. Допустим, есть функциональность на форуме, такая как “удалить пользователя”. Мы вводим в поле его имя и нажимаем на кнопку Submit. Передается запрос на сервер и мы работаем с обычным Statement, ну и для создания запроса используем конкатенацию:

Sring query = "delete from users where username=" + username;

Запомните раз и навсегда, что это плохо! Если какой-то злоумышленник в поле на форме введет следующую строку: vasia' or 'a'='a, это приведет к печальным последствиям. Результирующий запрос будет следующим:
delete from users where username='vasia' or 'a'='a'

Т.к. ‘a’ всегда ровняется ‘a’, то выражение в where всегда будет true и в результате запроса удалятся все записи из таблицы. Для избежания подобного, нужно заескейпить входящую строку. Это значит, что все входящие символы, если они представляют собой что-то, что для СУБД представялется значащим символом (например, кавычки), будут заменены на какую-то другую комбинацию символов. Делать это можно самому, можно использовать уже существующие методы/библиотеки, но в конце концов запрос выходит приблизительно такой:
delete from users where username='vasya\' or \'a\'=\'a'

То есть все кавычки в строке заменены на ', что для MySQL будет значит “воспринимай ковычку как часть строки”. Однако зачем нам это делать самим, когда все уже сделано для нас с помощью PreparedStatement? Используя его запрос будет выглядет следующим образом:
delete from users where username=?

А затем мы укажем параметр: preparedStatement.setString(1, username) И все переданное сюда будет восприниматься исключительно как текст, СУБД сама все заескейпит.

Пишите свои вопросы и комментарии, ибо чует душа - сложно и запутано написано, будем переписывать если так оно и окажется.


* Некоторые драйвера, иже не поддерживают пред-компиляцию, отсылают запрос только на этапе executeQuery().
** Заметьте, что при создании обычного Statement, никакой строки в объект соединения не передается, что значит, что они создаются каждый раз наново.
*** На самом деле некоторые JDBC драйвера (такие как Oracle) могут кешировать и обычные statement’ы. В случае Oracle JDBC Driver для этого нужно дергать implementation-specific API и оно не будет столь же эффективно, ну и плюс там есть свои заморочки. Это называется explicit statement caching.
**** Конечно может и не для всех, все я не смотрел, но для 3 СУБД из проверенных - это правда.

PS: огромной спасибо нашему Vlad’у за его блог пост по заказу, который очень помог в написании данной статьи.
PPS: Обсуждение на тему: PreparedStatements. Both DBMS & Java caching

Read more