CODEWITHSUNDEEP
javasqlsql-injection
M Sach
M Sach

Reputation: 34424

SqlInjection with prepared statement without bind variable?

As we know the best way to avoid sql injection is using prepared statement with bind variables. But i have question what if i use just prepared statement but not bind variables like below where customer id is coming from User interface

String query ="select * from customer where customerId="+customerId;
PreparedStatement stmt = con.prepareStatement(query); //line1

Does line 1 take care restricting sql injection even when i have not used bind variables?

I agree the best way is below but if above approach also takes care of restrcting sql injection then i would prefer above one(as its a legacy project)

String query ="select * from customer where customerId=?";
  PreparedStatement stmt = con.prepareStatement(query); 
  stmt.setInt(1, 100);

Is prepared statement without using bind variable sufficient to make sure sql injection not possible?

Upvotes: 1

Views: 1978

Answers (3)

RightHandedMonkey
RightHandedMonkey

Reputation: 1728

I know this is an older post, I just wanted to add that you avoid injection attacks if you can make sure you are only allowing integers into your query for line 1. String inputs are where the injection attacks happen. In the sample above, it is unclear which class of variable 'customerId' is, although it looks like an int. Since the question is tagged as Java, you can't do an injection attack with an int, so you should be fine.

If it is a string in line 1, you need to be confident that the 'customerId' comes from a secure source from which it must be an integer. If it comes from a post form or other user generated field then you can either try to escape it or convert it to an integer to be sure. If it is a string, cast it to an integer and you will not need to bind params.

Upvotes: -1

Your Common Sense
Your Common Sense

Reputation: 157839

One have to distinguish several matters.

Using prepared statement won't do any help just by itself.
As well as there is no harm in using non-prepared way in general.

The thing works only when you need to insert dynamical part into query. So, in this latter case such a dynamical part have to go into query via placeholder only, which actual value have to be bound later (placeholder is a ? or any other mark that represents the actual data in the query).

The very term "prepared statement" implies using placeholders for all the dynamical data that goes into query. So,

  • if you have no dynamical parts in the query, there would be obviously no injection at all, even without using prepared statements.
  • if you're using a prepared statement, but inject values directly into query instead of binding them - it would be wide open to injection.

So, again - only with placeholders for all dynamical data prepared statement would work. And it works because:

  • every dynamical value have to be properly formatted
  • prepared statement makes proper formatting (or handling) inevitable.
  • prepared statement does proper formatting (or handling) in the only proper place - right before query execution, not somewhere else, so, our safety won't rely on such unreliable sources like
    • some 'magic' feature which rather would spoil the data than make it safe.
    • good will of one (or several) programmers, who can decide to format (or not to format) our variable somewhere in the program flow. That's the point of great importance.
  • prepared statement affects the very value that is going into query, but not the source variable, which remains intact and can be used in the further code (to be sent via email or shown on-screen).
  • prepared statement can make application code dramatically shorter, doing all the formatting behind the scenes (*only if driver permits).

Upvotes: 1

Igor S.
Igor S.

Reputation: 3350

Line 1 will not check if develeper want or not want to drop table. If you write query it's assumed it is Ok.

Goal of sql injection is to prepare values that allows making additional sql query without will nor knowledge of developer. Quering your website with fake values in attributes. Example:

        id = "'); DROP ALL TABLES; --";
        query = "select * from customer where customerId="+id;

PreparedStatement ensures that special symbols (like ' or ") added to query using setInt/setString/etc will not interfere with sql query.

Upvotes: 1

Related Questions