Monday, July 30, 2012

Defend against SQL Injection using ActiveJDBC


I was asked on forums how ActiveJDBC defends against SQL Injection attacks. My first reaction was: hey, this is not an ORM problem, talk to web devs :)
Luckily, Lukas Eder wrote a nice blog that helped put things into perspective. While SQL Injection attacks are not the task of ORMs per se, ORMs still need to provide some level of advice on how to deal SQL injection pitfalls.
Lukas even offered some actual code snippets on his article. What is a developer to do to try things out?  HE/she writes tests, and so I proceeded to write a test using some of the examples of his code.
The first test had this code:

String id = "1';drop table users;select * from people where name = 'John";
List<User> users = User.where("id = '" + id + "'");
users.size();
User user1 = new User();
user1.set("first_name", "John", "last_name", "Doe", 
           "email", "john@doe.com").saveIt();
User.findAll().dump();
At first when I tried this against MySQL, I got "invalid statement" exception, apparently it could not handle the semicolon in the middle of the statement. After that, I ran this same code against H2 database, and sure enough, the SQL injection worked. This resulted in an exception:


Caused by: org.h2.jdbc.JdbcSQLException: Table "USERS" not found; SQL statement:
INSERT INTO users (email, first_name, last_name) VALUES (?, ?, ?) [42102-171]

Holy crap! My table USERS is gone!

This is bad news for those people who use a simple string concatenation to make SQL statements in public facing web projects.

However, I quickly wrote a second test that uses dynamic parameters passed to a model:

String id = "1';drop table users;select * from people where name = 'John";
List<User> users = User.where("id = ?", id);
users.size();
User user1 = new User();
user1.set("first_name", "John", "last_name", "Doe", 
        "email", "john@doe.com").saveIt();
User.findAll().dump();
As you can see here, the only difference is that I pass the "name" parameter as a dynamic one, instead of simply concatenating strings

The output from this test is:

Model: org.javalite.activejdbc.test_models.User, table: 'users', attributes: {EMAIL=john@doe.com, FIRST_NAME=John, ID=1, LAST_NAME=Doe}

As you can see, the table USERS is intact, no harm is done.

So, what is the take out from here? String concatenation to build dynamic queries in web applications is  evil!

But, since ActiveJDBC uses PreparedStatement, you are safe as long as you use dynamic parameters instead of splicing strings together. 

I actually never use string concatenation, not because I constantly worry about SQL Injection attacks, but simply because this makes for some ugly spaghetti code and this probably one of the reasons I did not pay much attention to it before.

Cheers to safe coding!

Sunday, April 8, 2012

Just how thin can a framework be? ActiveJDBC vs Hibernate.

When people talk about thickness of a framework, and it being lightweight, what do they actually mean?

I think there needs to be a few parameters to be looked at, for instance:
  • Level of intrusiveness into your code (subjective)
  • Speed of execution (objective)
  • Code elegance (subjective)
  • Physical weight - total size of dependencies for "Hello world" (objective)
  • Stack depth (objective) - depth of a stack in case of exception coming from a lower level technology

Lets consider the level of intrusiveness with Hibernate and ActiveJDBC. I personally of course prefer ActiveJDBC because it has almost no annotations, no configuration files, but most of all it has no third party object such as Hibernate Session that actually operates on entites.

Here is a code in Hibernate entity:

@Table(name = "employees")
public class Employee implements Serializable {
    public Employee() {}
    @Id
    @Column(name = "id")
    @GeneratedValue
    Integer id;
    @Column(name = "first_name")
    String firstName;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getFirstName() {
        return firstName;
    }
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", first_name='" + firstName + '\'' +
                '}';
    }
}



Usage of entity:
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
Session session = sessionFactory.openSession();
List employees = session.createQuery("select e from Employee as e").list();
session.close();

And here is comparable code in ActiveJDBC:

Model:
public class Employee extends Model{}

That is right! One line of code, and nothing else.

Usage of model:
Base.open("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/test_db", "user1", "*****");
List employees = Employee.findAll();
Base.close();

As you can see, ActiveJDBC uses a different paradigm: models (entities in AJ talk) operate
on self, without requiring a third party class.

I think that by code intrusiveness, ActiveJDBC is certainly thinner than Hibernate.

Speed of execution: I built a simple non-scientific test in both ActiveJDBC and Hibernate.
The code inserts 50 thousand records into MySQL table, then reads all 50K records from it.
This is performed on my laptop, which is: MBP 2009, Core 2 Duo CPU P8700 @ 2.53GHz,
with 8G RAM, 256G SSD and Ubuntu running natively.
Hibernate insert: 16057 milliseconds
ActiveJDBC insert: 9630 milliseconds

Hibernate select: 50000 records in: 1874 milliseconds
ActiveJDBC select: 50000 records in: 836 milliseconds

As you can see, ActiveJDBC is significantly faster given exactly the same task:
Insert: ActiveJDBC took 40% less time.
Select:ActiveJDBC took 54% less time.

Code elegance: while this is super subjective especially considering the fact that I'm the author of ActiveJDBC API, I hope most people will agree with me simply by looking at code above.

Physical weight: This is a total size of all dependencies a simple application requires.
Here ActiveJDBC wins hands down.
ActiveJDBC dependencies:

-rw-r--r-- 1 igor igor 137026 2012-04-05 12:03 activejdbc-1.2-SNAPSHOT.jar
-rw-r--r-- 1 igor igor 33795 2012-04-05 12:03 javalite-common-1.2-SNAPSHOT.jar
-rw-r--r-- 1 igor igor 495944 2012-04-05 12:03 mysql-connector-java-5.0.4.jar
-rw-r--r-- 1 igor igor 23659 2012-04-05 12:03 slf4j-api-1.5.10.jar
-rw-r--r-- 1 igor igor 7599 2012-04-05 12:03 slf4j-simple-1.5.10.jar

Total size: 704K

Hibernate dependencies:
-rw-r--r-- 1 igor igor 443432 2012-04-05 12:13 antlr-2.7.6.jar
-rw-r--r-- 1 igor igor 26361 2012-04-05 12:13 asm-1.5.3.jar
-rw-r--r-- 1 igor igor 16757 2012-04-05 12:13 asm-attrs-1.5.3.jar
-rw-r--r-- 1 igor igor 282338 2012-04-05 12:13 cglib-2.1_3.jar
-rw-r--r-- 1 igor igor 175426 2012-04-05 12:13 commons-collections-2.1.1.jar
-rw-r--r-- 1 igor igor 38015 2012-04-05 12:13 commons-logging-1.0.4.jar
-rw-r--r-- 1 igor igor 313898 2012-04-05 12:13 dom4j-1.6.1.jar
-rw-r--r-- 1 igor igor 208048 2012-04-05 12:13 ehcache-1.2.3.jar
-rw-r--r-- 1 igor igor 2321639 2012-04-05 12:13 hibernate-3.2.7.ga.jar
-rw-r--r-- 1 igor igor 365546 2012-04-05 12:13 hibernate-annotations-3.5.6-Final.jar
-rw-r--r-- 1 igor igor 66426 2012-04-05 12:13 hibernate-commons-annotations-3.0.0.ga.jar
-rw-r--r-- 1 igor igor 2566731 2012-04-05 12:13 hibernate-core-3.5.6-Final.jar
-rw-r--r-- 1 igor igor 100884 2012-04-05 12:13 hibernate-jpa-2.0-api-1.0.0.Final.jar
-rw-r--r-- 1 igor igor 8812 2012-04-05 12:13 jta-1.0.1B.jar
-rw-r--r-- 1 igor igor 495944 2012-04-05 12:13 mysql-connector-java-5.0.4.jar
-rw-r--r-- 1 igor igor 52150 2012-04-05 12:13 persistence-api-1.0.jar
-rw-r--r-- 1 igor igor 22338 2012-04-05 12:13 slf4j-api-1.5.6.jar
-rw-r--r-- 1 igor igor 7583 2012-04-05 12:13 slf4j-simple-1.5.6.jar

Hibernate requires a whopping 7424K, more than 10 times of ActiveJDBC! Even if I remove MySQL driver, this comparison is still not in favour of Hibernate.

Stack depth: this is simply counting methods on the stack trace, which gives you a pretty good idea of a framework depth. In order to see this for ORM, I mangled the SQL to cause an exception in the DB layer, and here are results:

Hibernate depth:
1276 [main] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 1054, SQLState: 42S22
1276 [main] ERROR org.hibernate.util.JDBCExceptionReporter - Unknown column 'age' in 'where clause'
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2536)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
at org.hibernate.loader.Loader.list(Loader.java:2271)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:452)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
at hibernate_test.SelectAllHibernate.main(SelectAllHibernate.java:13)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'age' in 'where clause'
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3176)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1266)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
at org.hibernate.loader.Loader.doQuery(Loader.java:802)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
at org.hibernate.loader.Loader.doList(Loader.java:2533)
... 13 more


ActiveJDBC depth:
Exception in thread "main" org.javalite.activejdbc.DBException: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right
syntax to use near 'where age > 1' at line 1, Query: SELECT * FROM employees WHERE where age > 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3176)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1266)
at org.javalite.activejdbc.DB.find(DB.java:408)
at org.javalite.activejdbc.LazyList.hydrate(LazyList.java:304)
at org.javalite.activejdbc.LazyList.size(LazyList.java:454)
at activejdbc_test.SelectAllActiveJDBC.main(SelectAllActiveJDBC.java:13)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use near 'where age > 1' at line 1
... 16 more


When you count the number of lines in exception stack trace betwen the client code (your code) and low level technology
(MySQL in this case), you will have an idea of a framework depth. For ActiveJDBC it is 3, for Hibernate it is 15.
So, Hibernate is about 5 times thicker than ActiveJDBC.

One might say: so what, why do I care about the size of dependencies, depth of stack trace, etc. I think a good developer
should care about these things. The thicker the framework, the more complex it is, the more memory it allocates,
the more things can go wrong. Besides, simply by requiring certain dependencies, it can limit your options
of using up to date versions of the same dependencies, creating headaches on classpath.

Bottom line is: choose your frameworks wisely

cheers