Does the preparedStatement avoid SQL injection?
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:
Mohamed Saligh
Updated on January 22, 2021Comments
-
Mohamed Saligh over 3 years
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?
-
Mohamed Saligh over 13 yearsDoes the setString make any difference? what it actually does? Even thats going to substitute the string inplace. What different it makes?
-
darioo over 13 years@Mohamed: it makes a difference. The query
"SELECT * FROM users WHERE name = ?"
will be sent to the database where it's compiled and thenuserName
fromsetString
will be substituted. If the database sees an illegal value, it will throw an error. So,' or '1'='1
will be treated as a whole string, and not as a statement involving operatorsor
and=
. The database will see it as a string with value"' or '1'='1"
.