1

I am working with java + mysql. I have the following piece of code:

String sql = "INSERT INTO tableA (id, age) VALUES (1, ?)";

PreparedStatement statement = connection.prepareStatement(sql);
stmt.setString(age);
stmt.execute();

Assuming that the String age :-( is an input from user, I would hope that the above code prevents SQL injection. I found out that mysql driver does not allow to multiple queries in one statement, so inputs like

76); Drop table tableB; -- )

would be rejected by mysql, even if I am not using prepared statements. However, in the latter case an input like 

(select id from tableB where age = 10) // lets assume this returns 20

would be accepted inserting (1, 20) in the database. By using preparedStatement in the way I have described above, I am expecting that this kind of input would get rejected preventing a SQL Injection attack. However, what I see is very weird. I am not sure if this subquery is executed or not, but what I see is a record (1, 0) being inserted in the database. I am expecting that I should rather get an exception from mysql here.

At this point I am totally confused. Any thoughts? Am I correct with my assumptions ? Thanks for your help.

Nishant Nagwani
  • 1,160
  • 3
  • 13
  • 26
  • I would really appreciate if I may know the reason for downvote? – Nishant Nagwani Apr 25 '13 at 23:58
  • 2
    When using a prepared statement, the statement is pre-set and you are binding values to the parameters, not modifying the statement. In your case you are binding the String `(select id from tableB where age = 10)` to the parameter representing `age` and not modifying the sql statement structure in any way. Is `age` a numeric column? If so, implicit conversion is happening to the string resulting in a `0` value. Should be using `stmt.setInt(age);` not `setString`. Also, may want to store `birth_date` rather than `age` while you are at it so that an up-to-date `age` can always be computed. – Glenn Apr 26 '13 at 01:05
  • I think you are right Glenn. That is indeed the case (sorry about missing this info in the question). I also came down to quite the same conclusion. I was actually curious what kind of behavior would this code trigger if I pass in a subquery to the prepared statement. I am dealing with some pretty old code right now, and the age was coming in as a string. setInt(Integer.pasrseInt(age)) was throwing a NumberFOrmatException but as I said I was curious to see the preparedStatement behavior and its handling of subqueries. – Nishant Nagwani Apr 26 '13 at 01:32
  • I realized it later that when I am using setString for an int, mysql driver is probably executing something like Integer.parseInt before inserting data, thus inserting 0 for the input string. Further analysis with input "1 AND (select id from tableB where age = 10)" confirmed this when the preparedStatement query inserted 1. Thanks a ton for your help. – Nishant Nagwani Apr 26 '13 at 01:32

0 Answers0