We help IT Professionals succeed at work.

Prevent SQL Injection with a dynamic SQL statement

jackjeckyl
jackjeckyl asked
on
1,676 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.
Comment
Watch Question

in java or JSP code you could remove all single quotes from the string with String.replaceAll("'","");
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2007

Commented:
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
CERTIFIED EXPERT
Top Expert 2007

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

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

Author

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

Commented:
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?

Commented:
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.

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

Thanks :-)

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

Commented:
Thanks for your help, worked great.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.