Solved

Prevent SQL Injection with a dynamic SQL statement

Posted on 2009-03-31
11
1,644 Views
Last Modified: 2012-05-06
OK, I know and understand how to prevent SQL injection with prepared statements:

http://www.owasp.org/index.php/Preventing_SQL_Injection_in_Java

But... I have a web page that has text boxes that are dynamically built.  Then a SQL statement is built off those text boxes that are created.  So not knowing how many boxes are created how can I handle the prepared statements inside a while loop?  Any good ideas or example would be fan-friggin-tastic.  Thanks.
0
Comment
Question by:jackjeckyl
  • 4
  • 2
  • 2
  • +2
11 Comments
 
LVL 17

Expert Comment

by:Thomas4019
ID: 24034016
in java or JSP code you could remove all single quotes from the string with String.replaceAll("'","");
0
 
LVL 20

Accepted Solution

by:
Venabili earned 50 total points
ID: 24037872
>>how can I handle the prepared statements inside a while loop?
In the string of the query, just add a ? at the place of the value and then add the real value in a Vector/Array of some type. After this during the preparation, just set the values from the array to its places. Below is a very simple code (just to ilustrate what I mean - you may want to keep also the types of the values for example)

Thomas4019, the single quotes are the least of your problems when you allow text to be sent down directly to the DB. :)
String query = "something";

List list = new ArrayList();

PreparedStatement ps;

while (haveMoreBoxes){

    String value = getBoxValue();

    query += " and somecolumn=?";

    list.add(value);

}

ps = con.prepareStatement(query);

for(int i=0; i<list.size(); i++) {

    ps.setString(i+1, list.get(i));

}

Open in new window

0
 
LVL 26

Expert Comment

by:mrcoffee365
ID: 24037980
There are legitimate strings with single quotes in them, as anyone with a name database (O'Brian) can tell you.  So don't remove single quotes, escape them with a second single quote.

You construct a preparedstatement the same way that you would construct a normal query string.  Sun gives an example of how to add the values to a prepared statement in a loop:
http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html

But your problem is that you need to create the PreparedStatement in a loop as well.  I'm not sure you'll get much performance improvement from that, but it does help prevent sql injection.

If you are figuring out the text boxes in a while loop, then you'll have to do it twice with steps something like these:
1. loop to get the additions to the string that will be your query
2. call con.prepareStatement with the constructed string from step 1
3. loop to add the values to the preparedstatement
4. call ps.executeUpdate() to execute the preparedstatement
0
 
LVL 26

Expert Comment

by:mrcoffee365
ID: 24037990
Sorry - Venabili, you got in with a good code example while I was still playing around with my answer.
0
 
LVL 20

Expert Comment

by:Venabili
ID: 24038001
Happens in such questions :) Besides your explanation is a good one.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:jackjeckyl
ID: 24038679
I'll give Venabili's suggestion a stab and let you guys know.  Thanks.
0
 
LVL 2

Expert Comment

by:-epoch-
ID: 24039266
Venabil -

your solution is a good one and im sure one can expand on that example alot more, the problem comes in with complex flow control. as in a filter with 10-100 complex attributes. one can still use the example given but you will have to duplicate the logic used to build up the string in the first place.

do you have an answer regarding that?
0
 
LVL 20

Expert Comment

by:Venabili
ID: 24039344
just change it to fit.
The one above is very easy one - just ilustrating the idea. It is rarely  that easy :)
Basically you need a logic to build the query - regardless if you are going to use Prepared steatement or not.
Just instead of adding the real values to the string (escaping them and so on), add a ? and then add the real value in the array. If the condition does not have a value (like some date to be before now()), you do not add a ? so you do not add a parameter in the array either. Keep a second array with the types if there is anything besides Strings and that's it (and in this case you need to check this value and invoke the correct method accordingly). At the end just go through the array and set the values . In this way the position in the array will always correspond to the one in the logic - no need to repeat something :)

You do not have duplication - you just build it a bit differently.

0
 
LVL 2

Expert Comment

by:-epoch-
ID: 24039375
ahhhhh, sorry for that one I posted a bit quick before actually understanding the logic you are using.. I understand completely now

Thanks :-)
0
 
LVL 20

Expert Comment

by:Venabili
ID: 24039418
It's ok :) The example was built around what the Asker said his scenario is -- in the easiest possible, almost not-real code way. mrcoffee365 explained the same thing as I did  - we just posted the same idea at the same time. And short of some variations, this is the only way to build such a thing :)
0
 

Author Closing Comment

by:jackjeckyl
ID: 31565079
Thanks for your help, worked great.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now