Users of jOOQ, PL/SQL, T-SQL are spoiled as they hardly ever need to worry about bind values. Consider the following statements:
Using jOOQ
public int countActors(String firstName, String lastName) {
return ctx.selectCount()
.from(ACTOR)
.where(ACTOR.FIRST_NAME.eq(firstName))
.and(ACTOR.LAST_NAME.eq(lastName))
.fetchOneInto(int.class);
);
}
The method parameters
firstName and
lastName will be automatically mapped to bind values in the generated SQL statement. Here’s the debug log output when running the above, where the first statement is sent to the JDBC driver and then to the database, wheas the second statement is generated for debugging purposes only:
Executing query :
select count(*)
from "SAKILA"."ACTOR"
where (
"SAKILA"."ACTOR"."FIRST_NAME" = ?
and "SAKILA"."ACTOR"."LAST_NAME" = ?
)
-> with bind values :
select count(*)
from "SAKILA"."ACTOR"
where (
"SAKILA"."ACTOR"."FIRST_NAME" = 'SUSAN'
and "SAKILA"."ACTOR"."LAST_NAME" = 'DAVIS'
)
Fetched result : +-----+
: |count|
: +-----+
: | 2|
: +-----+
With this API design, users don’t have to worry about binding variables explicitly at all, nor about remembering bind variable indexes or the data type of a bind value. This works because the overloaded
Field<T>.eq(T) method (as well as pretty much every other method that works in a similar way) internally delegates to the more generic
Field<T>.eq(Field<T>) method by wrapping the argument value in an explicit bind variable expression
DSL.val(T) in the jOOQ SQL expression tree.
Using PL/SQL
The same is true when you’re using PL/SQL (or any other stored procedure language of another database), for instance:
SET SERVEROUTPUT ON
DECLARE
FUNCTION count_actors (
p_first_name VARCHAR2,
p_last_name VARCHAR2
) RETURN NUMBER IS
v_result NUMBER(10);
BEGIN
SELECT count(*)
INTO v_result
FROM actor
WHERE first_name = p_first_name
AND last_name = p_last_name;
RETURN v_result;
END count_actors;
BEGIN
dbms_output.put_line(count_actors('SUSAN', 'DAVIS'));
END;
/
To be sure what happened, let’s consider the execution plan:
SELECT p.*
FROM (
SELECT *
FROM v$sql
WHERE upper(sql_text) LIKE 'SELECT COUNT(*) FROM ACTOR%'
ORDER BY last_active_time DESC
FETCH NEXT 1 ROW ONLY
) s
CROSS APPLY TABLE(dbms_xplan.display_cursor(sql_id => s.sql_id)) p;
As you can see below, bind variables were generated for the SQL query that was embedded in the PL/SQL function:
SQL_ID 9dgammbskx5tx, child number 0
-------------------------------------
SELECT COUNT(*) FROM ACTOR WHERE FIRST_NAME = :B2 AND LAST_NAME = :B1
Plan hash value: 3384208144
----------------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | INDEX RANGE SCAN| IDX_ACTOR_NAME | 1 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LAST_NAME"=:B1 AND "FIRST_NAME"=:B2)
Why is it important? 1: SQL injection
I’ve noticed this time and again when talking to Java developers: Many developers are aware of the
risk of SQL injection when not using bind variables. This can happen when we would write dynamic SQL like this, e.g. using JDBC or jOOQ’s plain SQL API:
String sql =
"SELECT count(*) "
+ "FROM actor "
+ "WHERE 1 = 1 "
+ (firstName != null ? "AND first_name = " + firstName + " " : "")
+ (lastName != null ? "AND last_name = " + lastName + " " : "");
// JDBC
try (Statement s = connection.createStatement();
ResultSet rs = s.executeQuery(sql)) {
...
}
// jOOQ
Result<?> result = ctx.fetch(sql);
Seriously. Don’t do this. Ever! Always use bind values for user input. There’s hardly any reason at all why you should inline the values. I mean, of course, you could if you always remember to manually escape all strings, e.g.:
public static String escape(String string) {
// TODO: Handle MySQL's non-standard backslash escaping, too
return string == null ? null : string.replace("'", "''");
}
And then:
String sql =
"SELECT count(*) "
+ "FROM actor "
+ "WHERE 1 = 1 "
+ (firstName != null ? "AND first_name = " + escape(firstName) + " " : "")
+ (lastName != null ? "AND last_name = " + escape(lastName) + " " : "");
Notice that there’s still a vulnerability risk in MySQL, which doesn’t necessarily conform to standard SQL string literal escaping. A very unfortunate MySQL “feature”, which is handled correctly by jOOQ:
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_backslash_escapes
But why run the risk? It’s so much easier with bind values.
Why is it important? 2: Performance!
So, most Java developers, luckily, are aware of SQL injection vulnerabilities and mostly get this right. But there’s another thing that few Java developers are aware of, unfortunately. And that’s the performance aspect of using bind values. Let’s assume we’re not using bind values for the above query. Check this out:
SELECT count(*)
FROM actor
WHERE first_name = 'SUSAN'
AND last_name = 'DAVIS';
SELECT count(*)
FROM actor
WHERE first_name = 'NICK'
AND last_name = 'WAHLBERG';
And now, let’s find execution plans:
SELECT p.*
FROM (
SELECT *
FROM v$sql
WHERE upper(sql_text) LIKE 'SELECT COUNT(*) FROM ACTOR%'
ORDER BY last_active_time DESC
FETCH NEXT 2 ROWS ONLY
) s
CROSS APPLY TABLE(dbms_xplan.display_cursor(sql_id => s.sql_id)) p;
Result:
SQL_ID 12r8afykqkzbd, child number 0
-------------------------------------
SELECT count(*) FROM actor WHERE first_name = 'NICK' AND last_name = 'WAHLBERG'
Plan hash value: 3384208144
----------------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | INDEX RANGE SCAN| IDX_ACTOR_NAME | 1 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LAST_NAME"='WAHLBERG' AND "FIRST_NAME"='NICK')
SQL_ID gfppqr9gpjqws, child number 0
-------------------------------------
SELECT count(*) FROM actor WHERE first_name = 'SUSAN' AND last_name = 'DAVIS'
Plan hash value: 3384208144
----------------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | INDEX RANGE SCAN| IDX_ACTOR_NAME | 1 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LAST_NAME"='DAVIS' AND "FIRST_NAME"='SUSAN')
As you can see, the two almost identical queries produced two times the exact same execution plan (same plan hash value), but they are stored under two distinct
SQL_ID values. Yes, they’re distinct SQL statements!
Why?
Most sophisticated databases (including Oracle, SQL Server, DB2 and others) implement an execution plan cache. The reason for this is simple. Calculating an execution plan with cost based optimisation is expensive. Not for these trivial statements, but imagine you have dozens of JOINs, semi joins, unions, and what not. There are thousands of candidate execution plans, and the database needs to find the best one for you. That can be tons of work and we don’t want to let the database do that work every time.
So, the database will run a soft-parse (Oracle speak) to identify a SQL query and translate its SQL string to a
SQL_ID. It will then check if there’s already a suitable plan available for that particular
SQL_ID, and if so, it will avoid the so-called hard-parse (Oracle speak) to calculate a new plan.
Let me repeat this one more time: Making best use of this plan cache (Oracle speak: Cursor cache) is extremely important. You can severely overload a system up to the point of bringing it down, if you’re not using bind variables.
There’s a workaround to use CURSOR_SHARING=FORCE, which will transform inline values to bind values, but I’m not even going to explain how it works, because most Oracle experts advise you not to use that feature due to the significant drawbacks it will bring.
What if I want to inline values?
As we’ve seen above, when using jOOQ or PL/SQL, the above problems are really non-discussions, because it is quite unlikely that you run into a situation of accidentally inlining your bind values:
- In jOOQ, you’d have to use plain SQL
- In PL/SQL, you’d have to use dynamic SQL using
DBMS_SQL
Anyway, in rare cases, users want to inline their bind variables for them to appear directly in the resulting SQL statement. This is never the case for user input, for ID values, or for ordinary search values. But it can be the case when you query for discriminators or “enum” values (enforced through a
CHECK constraint, for instance), or when you run a report only once a year (plan is never available from the cache).
In these cases, it can be of advantage to not use bind values specifically to prevent the database from re-using a cached execution plan, because you
know that the cached plan, which works well for 90% of the queries, won’t work well for this particular bind value (or in the case of the once-per-year report, you might get a slightly better plan by giving the database more information).
An example, let’s insert 1×1 and 99999×0 into a table:
CREATE TABLE skewed (
v NUMBER(1)
);
INSERT INTO skewed
SELECT decode(level, 1, 1, 0)
FROM dual
CONNECT BY level <= 100000;
CREATE INDEX skewed_i ON skewed(v);
EXEC dbms_stats.gather_table_stats('TEST', 'SKEWED');
Now, clearly we see that when querying the table
SKEWED for values
V = 0, the index is useless, whereas it is very useful for values
V = 1. Let’s run this statement here:
SET SERVEROUTPUT ON
DECLARE
v_bind NUMBER(1);
v_result NUMBER(10);
BEGIN
v_bind := 0;
SELECT count(*)
INTO v_result
FROM skewed
WHERE v = v_bind;
FOR rec IN (
SELECT * FROM TABLE(dbms_xplan.display_cursor)
) LOOP
dbms_output.put_line(rec.plan_table_output);
END LOOP;
v_bind := 1;
SELECT count(*)
INTO v_result
FROM skewed
WHERE v = v_bind;
FOR rec IN (
SELECT * FROM TABLE(dbms_xplan.display_cursor)
) LOOP
dbms_output.put_line(rec.plan_table_output);
END LOOP;
SELECT count(*)
INTO v_result
FROM skewed
WHERE v = 1;
FOR rec IN (
SELECT * FROM TABLE(dbms_xplan.display_cursor)
) LOOP
dbms_output.put_line(rec.plan_table_output);
END LOOP;
END;
The above block runs the exact same statement three times:
- With a bind variable of 0 (can’t really use the index)
- With a bind variable of 1 (should be using the index)
- With an inline value of 1 (should be using the index)
Here’s the result:
SQL_ID 1q0qjm8za06w3, child number 0
-------------------------------------
SELECT COUNT(*) FROM SKEWED WHERE V = :B1
Plan hash value: 4055318479
--------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | INDEX FAST FULL SCAN| SKEWED_I | 99999 |
--------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("V"=:B1)
SQL_ID 1q0qjm8za06w3, child number 0
-------------------------------------
SELECT COUNT(*) FROM SKEWED WHERE V = :B1
Plan hash value: 4055318479
--------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | INDEX FAST FULL SCAN| SKEWED_I | 99999 |
--------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("V"=:B1)
SQL_ID bdpjxzqpg2416, child number 0
-------------------------------------
SELECT COUNT(*) FROM SKEWED WHERE V = 1
Plan hash value: 276090370
----------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | INDEX RANGE SCAN| SKEWED_I | 1 |
----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("V"=1)
Observe the estimated numbers of rows above (in red):
- In the first case, the estimate is correct. With
V = 0, we’ll get 99999 rows, so we might just as well scan the entire index to calculate the count value
- In the second case, we’ve inherited the cached execution plan from the first run, including the estimate of 99999 rows, which is clearly wrong in this case. The database should’ve estimated 1 row here
- In the third case, the estimate is again correct (note: different
SQL_ID, and we get an optimal plan
We’re just out of luck. If we had inversed the order of queries, we would have gotten the right estimate for
V = 1 but a wrong estimate for
V = 0…
Adaptive Cursor Sharing
Oracle knows some features to remedy the above problems.
Oracle 11g introduced adaptive cursor sharing, which means that if we re-execute the above statements, the database will have already figured out that in this particular case, the plans should depend on the actual bind variable, because in hindsight, the second plan was wrong for
V = 1
On a second execution of the previous block, we’ll see:
SQL_ID 1q0qjm8za06w3, child number 1
-------------------------------------
SELECT COUNT(*) FROM SKEWED WHERE V = :B1
Plan hash value: 4055318479
--------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | INDEX FAST FULL SCAN| SKEWED_I | 99999 |
--------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("V"=:B1)
SQL_ID 1q0qjm8za06w3, child number 2
-------------------------------------
SELECT COUNT(*) FROM SKEWED WHERE V = :B1
Plan hash value: 276090370
----------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | INDEX RANGE SCAN| SKEWED_I | 1 |
----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("V"=:B1)
SQL_ID bdpjxzqpg2416, child number 0
-------------------------------------
SELECT COUNT(*) FROM SKEWED WHERE V = 1
Plan hash value: 276090370
----------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | INDEX RANGE SCAN| SKEWED_I | 1 |
----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("V"=1)
As you can see above, the first two executions are still using the same plan, but they now have a new “child number” value, indicating that for any given
SQL_ID, there are now different candidate plan hash values, depending on the bind variable input that we’re getting.
This often works well with the following caveats:
- It only works after the plan was wrong at least once. This translates to at least one poor user who suffers from a slow query
- It only works if the plans remain in the cache (which isn’t the case for rarely executed queries). Once the plans are purged from the cache, we have to run the query again at least twice for the alternative “child numbers” to appear
- It stops working well if your query is much more complex, i.e. when complicated correlations exist between the bind variables that should each produce different plans
So, if in doubt, in this particular case, it’s not a bad idea to simply use an inline value / constant literal in the query directly, to help the optimiser make the right choice.
Another interesting article about this topic is here, where the optimiser will always be fooled by bind variables, and where you should always use inline values: When querying discriminators from views.
Adaptive Execution Plans
Oracle 12c took adaptive cursor sharing one step further and now supports adaptive execution plans, meaning that certain execution plans are known to be “shaky” in advance and the optimiser already pre-calculated a fallback plan that applies if the estimates are wrong for a given execution. In that case, the plan can be changed “in flight” and an alternative plan, which is better for a particular execution, is applied.
This feature is still not too stable in Oracle 12cR1, which is why some Oracle experts generally recommend turning it off.
Conclusion
You should always use bind values by default. In 99% of all cases, they’re the right choice for two reasons:
- SQL injection prevention (obvious)
- Statement caching optimisation (less obvious)
The latter reason is one that is not really well known among developers, because it’s not a problem that appears on development environments. It’s a production-only problem that happens under heavy load. Yet, you should be aware of this problem, and always remember to avoid generating too many distinct SQL strings (
see also this related article about IN lists)
In rare cases, it is better to use inline values / literals, as this will help the optimiser make a much better choice in a predictable manner. These cases include:
- Querying skewed data (unless adaptive features can be expected to kick in)
- Querying discriminators (in this case, it’s always advisable to use inline values)
Using languages like PL/SQL, T-SQL, pgplsql, or APIs like jOOQ definitely helps you get this right, because you don’t have to think about this problem anymore. You’ll get it right automatically.
Side-note: Hibernate’s Criteria Query
Like jOOQ, Hibernate supports a type safe DSL for constructing JPQL queries, which to some extent cover basic SQL functionality when querying entities. Hibernate historically chose quite interesting defaults (as of version 5.2.10):
- String values are always transformed to bind values, regardless if you’re using implicit values, explicit parameters, or explicit literals. The goal of this is to prevent SQL injection (because currently, Hibernate doesn’t auto-escape inline string literals)
- Implicit integer values are always inlined
- Explicit parameters are always sent as parameters
- Explicit literals are usually sent as literals (unless they’re strings)
The above implementation is unfortunate as we’ve seen before for these reasons:
- Bind values should always be the default, especially when using ID values, which are integers, and as such, often inlined in the current implementation. Luckily, this has been recognised as a bug and will be fixed, soon: https://hibernate.atlassian.net/browse/HHH-9576
- Literals should be sent to the server as literals, because when users need literals (e.g. in the above edge cases), they don’t want the API to override this behaviour through bind values. This might also be solved soon: https://hibernate.atlassian.net/browse/HHH-11778
I’m currently working with the team to remedy these problems, such that the criteria API won’t inhibit users from a performance perspective:
Like this:
Like Loading...
