Does using preparedStatement mean there will not be any SQL Injection?

Question

I have read that to prevent SQL Injection one must use PreparedStatement.
Does that mean if i am using perparedStatement then no one can perform SQL Injection in any of my page? Is it foolproof against SQL Injection? If not then please give some example to demonstrate this.

Answer 1

As long as you're actually using the parameter substitution feature of the prepared statement (it's possible to misuse them and not use that feature), and provided there isn't a bug in the prepared statement library you're using, then you should be fine against raw SQL injection. That doesn't mean you shouldn't treat whatever the user gives you with suspicion, though. :-)

Answer 2

Although Prepared Statements helps in defending against SQL Injection, there are possibilities of SQL Injection attacks through inappropriate usage of Prepared Statements.

The example below explains such a scenario where the input variables are passed directly into the Prepared Statement and thereby paving way for SQL Injection attacks.

String strUserName = request.getParameter("Txt_UserName"); 
PreparedStatement prepStmt = con.prepareStatement("SELECT * FROM user WHERE userId = '+strUserName+'");

prepared statement can be vulnerable to SQL injection if it is not done properly.

Answer 3

Using the prepared statement feature of the language provided means you are using a tried and tested solution for the problem - it doesn't mean that there are never any bugs or scope for SQL Injection possibilities, but what it does mean is that you are not the only person using the implementation. The more people using the same implementation for something means the more chances for bugs to be found and eliminated - if you use your own implementation then only you can find and fix the bugs.

Answer 4

prepared statements do not cover non-data parts of the query - identifiers and operators.
thus, if some of them are variable and being added to the query directly, injection is possible.

thanks to limited number of possible options, all variable identifiers should be chosen from pre-written variants based on user input. same for operators.
No user input should be added to the query directly.

Answer 5

Short answer: yes, if used properly.

However, this does not mean that there can't be bugs in the JDBC driver, opening up for SQL injection. When I looked into this for a company I worked at, I found that there was indeed an SQL injection bug in one of the JDBC drivers we used (PostgreSQL). This is some years ago, and the bug was fixed.

Although I don't remember the specifics, I recall looking at the source code for a JDBC implementation, and seeing that it was implemented with string concatenation.

I would expect this to be rare, though, and my advice would be to trust the implementation and use PreparedStatements properly.