Eliminating the problem
Posted Jun 1, 2006 13:27 UTC (Thu) by mrshiny
In reply to: Eliminating the problem
Parent article: SQL injection vulnerabilities in PostgreSQL
The thing is, SQL injection is trivial to prevent; simply use prepared statements with placeholders that are bound using an API. This improves the performance of the system AND increases security... Frankly I'm confused as to why you WOULDN'T use prepared queries.
Consider this example:
String sql = "select * from user_acct where username = '" + username + "' and password = '" + password + "'";
Statement stmt = connection.createStatement(sql);
In this case the database will get a new statement every time a new user tries to log in to the system. The database usually caches compiled statements and execution plans to increase performance, but here each statement will look different. Also, it's vulnerable to the SQL injection where the password is
' or 1 = 1--.
Now consider the following example:
String sql = "select * from user_acct where username = ? and password = ?";
PreparedStatement stmt = connection.prepareStatement(sql);
In this case, there is no chance for SQL injection, and furthermore the database can cache the SQL and execution plan on the server to increase performance, because for each user it is the same. Clearly anyone who DOESN'T use prepared statements is in need of some job training or a new career path.
to post comments)