Does the preparedStatement avoid SQL injection? [duplicate]

I have read and tried to inject vulnerable sql queries to my application. It is not safe enough. I am simply using the Statement Connection for database validations and other insertion operations.

Is the preparedStatements safe? and moreover will there be any problem with this statement too?

Solution 1:

Using string concatenation for constructing your query from arbitrary input will not make PreparedStatement safe. Take a look at this example:

preparedStatement = "SELECT * FROM users WHERE name = '" + userName + "';";

If somebody puts

' or '1'='1

as userName, your PreparedStatement will be vulnerable to SQL injection, since that query will be executed on database as

SELECT * FROM users WHERE name = '' OR '1'='1';

So, if you use

preparedStatement = "SELECT * FROM users WHERE name = ?";
preparedStatement.setString(1, userName);

you will be safe.

Some of this code taken from this Wikipedia article.

Solution 2:

The prepared statement, if used properly, does protect against SQL injection. But please post a code example to your question, so we can see if you are using it properly.

Solution 3:

Well simply using PreparedStatement doesn't make you safe. You have to use parameters in your SQL query which is possible with PreparedStatement. Look here for more information.

Solution 4:

The PreparedStatement alone does not help you if you are still concatenating Strings.

For instance, one rogue attacker can still do the following:

call a sleep function so that all your database connections will be busy, therefore making your application unavailable
extracting sensitive data from the DB
bypassing the user authentication

And it's not just SQL that can b affected. Even JPQL can be compromised if you are not using bind parameters.

Bottom line, you should never use string concatenation when building SQL statements. Use a dedicated API for that purpose:

JPA Criteria API
jOOQ