Prevent SQL Injection with a dynamic SQL statement

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

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.
Question by:jackjeckyl
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
LVL 17

Expert Comment

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

Accepted Solution

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=?";
ps = con.prepareStatement(query);
for(int i=0; i<list.size(); i++) {
    ps.setString(i+1, list.get(i));

Open in new window

LVL 27

Expert Comment

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:

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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

LVL 27

Expert Comment

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

Expert Comment

ID: 24038001
Happens in such questions :) Besides your explanation is a good one.

Author Comment

ID: 24038679
I'll give Venabili's suggestion a stab and let you guys know.  Thanks.

Expert Comment

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?
LVL 20

Expert Comment

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.


Expert Comment

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 :-)
LVL 20

Expert Comment

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 :)

Author Closing Comment

ID: 31565079
Thanks for your help, worked great.

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ejb stateless example 2 63
How to log java errors in tomcat 8 35
Read CLOB data from Oracle using JAVA 3 36
junit initializtion error 2 18
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
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…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses

738 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