Solved

Prevent SQL Injection with a dynamic SQL statement

Posted on 2009-03-31
11
1,645 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 27

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 27

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
looking for unix program to search some keywords in side EAR WAR and JAR files 10 81
github account with ecipse 1 42
egit plugin on eclipse 8 40
oracle 11g 23 50
For customizing the look of your lightweight component and making it look lucid like it was made of glass. Or: how to make your component more Apple-ish ;) This tip assumes your component to be of rectangular shape and completely opaque. (COD…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

895 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

12 Experts available now in Live!

Get 1:1 Help Now