41

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?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Mohamed Saligh
  • 12,029
  • 19
  • 65
  • 84
  • 1
    Prepared statements are the way to go. AFAIK a prepared statement would only be parsed once so there's no chance of SQL injection at a later date. Of course, you'll still need to sanitse input to protect against XSS attacks, etc. – CurtainDog Dec 02 '10 at 08:25

4 Answers4

66

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.

darioo
  • 46,442
  • 10
  • 75
  • 103
  • 10
    Does the setString make any difference? what it actually does? Even thats going to substitute the string inplace. What different it makes? – Mohamed Saligh Dec 02 '10 at 09:21
  • 23
    @Mohamed: it makes a difference. The query `"SELECT * FROM users WHERE name = ?"` will be sent to the database where it's compiled and then `userName` from `setString` 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 operators `or` and `=`. The database will see it as a string with value `"' or '1'='1"`. – darioo Dec 02 '10 at 09:24
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.

pts
  • 80,836
  • 20
  • 110
  • 183
1

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:

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
1

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.

Petar Minchev
  • 46,889
  • 11
  • 103
  • 119