Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Prevent SQL Injection with a dynamic SQL statement

Posted on 2009-03-31
11
Medium Priority
?
1,659 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 200 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses

688 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