SQL injection is a web application problem, and not directly related to an ORM. ActiveJDBC will process any SQL that is passed to it. (See the discussion here: https://groups.google.com/d/topic/activejdbc-group/5D2jhWuW4Sg/discussion)Is that really true? Should the database abstraction layer delegate SQL injection prevention to the client application?
SQL Injection BackgroundSQL injection is a problem that most of us developers have had to deal with one time or another in their professional lives. Wikipedia explains the problem nicely. Given the following piece of Java code (or any other language):
Imagine that “userName” is a variable taken from an HTTP request. Blindly pasting an HTTP request parameter gives way to simple attacks as these:
statement = "SELECT * FROM users WHERE name = '" + userName + "';"
This doesn’t happen to you? Maybe not. But the problem is seen quite often on Stack Overflow. More than 2000 results when searching for “SQL injection”: https://stackoverflow.com/search?q=sql+injection. So even if you know how to prevent it, someone on your team may not. OK, but … it’s not that bad if one out of 500 statements is badly written by some programmer that was oblivious of this threat? Think again. Have you ever heard of a tool called sqlmap? This tool will find any problematic page within your application within a couple of seconds/minutes/hours, depending on the severity of your injection problem. Not only that, once it has found problematic pages, it will be able to extract ALL kinds of data from your database. I mean ALL kinds of data. A selection of sqlmap features:
-- attacker sends this code in the userName field: userName = "a';DROP TABLE users; SELECT * FROM userinfo WHERE 't' = 't" -- resulting in the following statement: statement = "SELECT * FROM users WHERE name = 'a';" + "DROP TABLE users;" + + "SELECT * FROM userinfo WHERE 't' = 't';"
Yes! If you suffer from SQL-injection-unsafe code, an attacker can seize your server under some circumstances!! In our company, we’ve tried sqlmap in a sandbox environment against some open source blog software with known vulnerabilities. We’ve managed to seize the server in no time, without writing a single line of SQL
- Support to enumerate users, password hashes, privileges, roles, databases, tables and columns.
- Support to search for specific database names, specific tables across all databases or specific columns across all databases’ tables. This is useful, for instance, to identify tables containing custom application credentials where relevant columns’ names contain string like name and pass.
- Support to download and upload any file from the database server underlying file system when the database software is MySQL, PostgreSQL or Microsoft SQL Server.
- Support to execute arbitrary commands and retrieve their standard output on the database server underlying operating system when the database software is MySQL, PostgreSQL or Microsoft SQL Server.
Database Abstraction and SQL InjectionOK, now that I have your attention, let’s think again about what Igor Polevoy said:
SQL injection is a web application problem, and not directly related to an ORM. ActiveJDBC will process any SQL that is passed to it.Yes, he may be right. Given that ActiveJDBC is a slim wrapper for JDBC, allowing to do nice CRUD simplifications, such as these (taken from their website):
Did you spot the risk for SQL injection? Right. Even if it uses bind values for underlying PreparedStatements, this tool is as unsafe as JDBC. You can avoid SQL injection, if you’re careful. Or you can start concatenating strings all over. But you have to be aware of that! How does jOOQ handle situations like these? The jOOQ manual explains how bind values are handled explicitly or implicitly. Here are some examples:
List<Employee> people = Employee.where("department = ? and hire_date > ? ", "IT", hireDate) .offset(21) .limit(10) .orderBy("hire_date asc");
The above examples will yield
// Implicitly creating a bind value for "Poe" create.select() .from(T_AUTHOR) .where(LAST_NAME.equal("Poe")); // Explicitly creating a (named) bind value for "Poe" create.select() .from(T_AUTHOR) .where(LAST_NAME.equal(param("lastName", "Poe"))); // Explicitly inlining "Poe" in the generated SQL string create.select() .from(T_AUTHOR) .where(LAST_NAME.equal(inline("Poe")));
In the case where “Poe” is inlined, escaping is handled by jOOQ, to prevent syntax errors and SQL injection. But jOOQ also supports injecting SQL strings directly in generated SQL. For instance:
SELECT * FROM T_AUTHOR WHERE LAST_NAME = ? SELECT * FROM T_AUTHOR WHERE LAST_NAME = ? SELECT * FROM T_AUTHOR WHERE LAST_NAME = 'Poe'
In this case, SQL injection can occur just as with JDBC
// Inject plain SQL into jOOQ create.select() .from(T_AUTHOR) .where("LAST_NAME = 'Poe'");
ConclusionIn essence, Igor is right. It is the (client) application developer’s responsibility to be aware of SQL injection issues created by their code. But if a database abstraction framework built on top of JDBC can avoid SQL injection as much as possible in its API, all the better. From a SQL injection perspective, database abstraction frameworks can be divided into three categories:
- Simple utilities. These include Spring’s JdbcTemplate, or Apache’s DbUtils. They really just enhance the JDBC API with convenience (less exception handling, less verbosity, simpler variable binding, simpler data fetching). Of course, these tools won’t prevent SQL injection
- Full SQL abstraction. These include jOOQ, JaQu, JPA’s CriteriaQuery and others. Their normal operation mode will always render bind values in generated SQL. This prevents SQL injection in most cases.
- The others. Many other frameworks (including ActiveJDBC and Hibernate) are mainly based on (SQL or HQL) string operations. While they abstract many SQL-related things, they do not prevent SQL injection at all.