File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes how to prevent sql injection Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to prevent sql injection" Watch "how to prevent sql injection" New topic
Author

how to prevent sql injection

paul reberg
Greenhorn

Joined: Sep 01, 2010
Posts: 7
Hi. I've run WatchFire against my app, and I'm not sure how to fix all these sql injection issues. How would I go about this? Thanks!
Mike Peters
Ranch Hand

Joined: Oct 10, 2009
Posts: 67

Use parameters in PreparedStatements. See http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html


Mike Peters
Bill Karwin
author
Ranch Hand

Joined: Aug 02, 2010
Posts: 32
Using prepared statements with parameters helps to defend against SQL injection in most common cases, when you would otherwise interpolate untrusted content into a string and then execute the string as an SQL statement.

But a query parameter takes the place of a single value. You can't use a query parameter as a substitute for a dynamic table name, column name, list of values (e.g. for an IN() predicate), expressions, or SQL keywords.

For those cases, you can use techniques like filtering or whitelisting so you don't interpolate untrusted content into your SQL strings.

Filtering is where you strip out any characters that would cause trouble. If you know your dynamic column name should only be alphanumeric characters, then apply a filter to your variable before using it in SQL. Or else just reject a variable if it doesn't match a regular expression like /^[A-Za-z0-9]*$/

Whitelisting is where you use a Map<String,String> so you can transform the untrusted input into known safe values. If a user inputs a column name that doesn't appear as a key in the Map, then use a default or else raise an error.


Bill Karwin is the author of SQL Antipatterns: Avoiding the Pitfalls of Database Programming
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30515
    
150

Bill,
Or batch the in clause and do use prepared statements. I do not use filtering/whitelisting. I use a true prepared statement with specific numbers of parameters in each. (The article I wrote for the JavaRanch Journal a few years ago explains it more.)

Filtering/whitelisting isn't always possible. And in our case it cost us a performance hit with not using the binding variables and having a different query each time. My approach doesn't have that issue.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Bill Karwin
author
Ranch Hand

Joined: Aug 02, 2010
Posts: 32
Hi Jeanne,

Yes, I do recommend using parameters when the dynamic part of the query is a value.

I have an example in my book of adding a list of ? parameter placeholders to a query string and preparing that. Your batching idea takes that a step further, and gives benefit in some vendors of RDBMS, where the optimization of a given SQL query is cached. Other databases don't cache optimization plans, so there's no benefit in that way.

But as I said, you can't use parameter placeholders for things other than values. If your dynamic query involves dynamic table name, column name, expressions, or SQL keywords, you have to rely on string interpolation. And that's where it's important to prevent untrusted input from getting into your string.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30515
    
150

I agree that it only works for values.
Mike Peters
Ranch Hand

Joined: Oct 10, 2009
Posts: 67

I wonder, in what scenario, do you want a user to be able to alter a query component other than the parameters (except for a query tool)? In other words, when are PreparedStatement parameters not enough?

Thanks in advance.
Lester Burnham
Rancher

Joined: Oct 14, 2008
Posts: 1337
Mike Peters wrote:in what scenario, do you want a user to be able to alter a query component other than the parameters (except for a query tool)? In other words, when are PreparedStatement parameters not enough?

An app I worked on stored monthly data of a particular process in separate tables named "process_totals_2010_7", "process_totals_2010_8" etc. (For various reasons it was impractical to store them all in one table and select the data via month/year keys.) In that case, you need to vary the table name.
Bill Karwin
author
Ranch Hand

Joined: Aug 02, 2010
Posts: 32
Mike Peters wrote:I wonder, in what scenario, do you want a user to be able to alter a query component other than the parameters (except for a query tool)?


I was having a discussion with a security expert and he posed me the same question. He even asserted that I'd be "hard pressed" to come up with any realistic example.

I showed him a query like the following:

SELECT * FROM Products WHERE ...criteria... ORDER BY {$Column} {$AscOrDesc}

This is a pretty common pattern where you want the user's choice to determine the sort order and sort direction. For example on Amazon.com you can sort by average customer review, or by price low to high or by price high to low, etc.

The trick is that you can't use a query parameter for column names or SQL keywords (e.g. ASC vs. DESC). You can use a query parameter only in a context where you would otherwise use a single literal value, like a number or a quoted string literal or quoted date literal.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30515
    
150

Right. In which case you are left building a query from parameters passed in. Ideally translating them. Like pass "true" or "false" rather than "asc" or "desc" and let the code create the value SQL. For Lester's example, one has to validate a number was passed in or a valid table name.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Bill Karwin wrote:
SELECT * FROM Products WHERE ...criteria... ORDER BY {$Column} {$AscOrDesc}

This is a pretty common pattern where you want the user's choice to determine the sort order and sort direction. For example on Amazon.com you can sort by average customer review, or by price low to high or by price high to low, etc.


However, in this case you probably don't put in the SQL text arbitrary strings typed by the user, but choose one of compile-time constants based on the user's selections. Therefore the sql injection shouldn't be an issue here and you don't haveto sanitize the inputs.

Besides, the database itself could help. Eg. in Oracle 10 and above you can use the dbms_assert package. However, do not use these functions to sanitize literals. Always use binds and you will be on the safe side.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19693
    
  20

Martin Vajsar wrote:
Bill Karwin wrote:
SELECT * FROM Products WHERE ...criteria... ORDER BY {$Column} {$AscOrDesc}

This is a pretty common pattern where you want the user's choice to determine the sort order and sort direction. For example on Amazon.com you can sort by average customer review, or by price low to high or by price high to low, etc.


However, in this case you probably don't put in the SQL text arbitrary strings typed by the user, but choose one of compile-time constants based on the user's selections. Therefore the sql injection shouldn't be an issue here and you don't haveto sanitize the inputs.

On the web, no matter how the user provides the input, never trust it. I can do a POST request with other values without any problems. Validation doesn't have to be as extended as other inputs, but it still needs to be there. A simple user-input-value-to-actual-field mapping is usually enough. I think that's what you mean, but I just wanted to clarify.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
R. Grimes
Ranch Hand

Joined: Aug 23, 2009
Posts: 42
While all the programmatic suggestions here are good, I believe the best way to prevent SQL Injection attacks, along with all sorts of other attacks, is by placing a WAF (web application firewall) in front of your servers. Personally, I like ArmorLogic's Profense, but there are lots of alternatives. You can set it in LEARN mode at first, and then eventually set it to accept only white-listed requests. I feel that profiled requests that are white-listed are the best way to filter out malicious requests.

Ron Grimes
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

... I believe the best way to prevent SQL Injection attacks ... is by placing a WAF (web application firewall) in front of your servers.

I profoundly disagree on this.

While the firewall is certainly indispensable in preventing other sorts of attacks, the only effective way to prevent SQL injection is to use bind variables and never ever concatenate texts that came from the internet into an SQL query (not even into an ORDER BY clause, as mentioned above). That way you can safely process in the database anything the user entered or otherwise sent to your application, and you know for sure the your application is not prone to SQL injection.

The WAFs are highly configurable and probably can filter and block malicious user inputs, however the SQL injection attacks may be pretty ingenious, using all sorts of obfuscation to pass past the filters. I've seen a hex-encoded attack string some time ago. Moreover, setting the filter strictly may result in refusing valid user inputs (imagine Google refusing to process texts like "DROP TABLE AUDIT"), and might result in duplicating the application logic in the firewall in some way to distinguish legitimate and malicious requests.

I would discourage anyone from relying on WAFs to prevent SQL injection. It might be a temporary patch if the application is not coded properly, but the only real solution is to fix the app. Using binds has the additional benefit of making the application more scalable and responsive, as in most databases reusing commands with binds saves processing time by skipping the parsing of the query.
Ravi Kiran Va
Ranch Hand

Joined: Apr 18, 2009
Posts: 2234

To avoid SQL Injection means

simply , check the data sent that is sent , rather than blindly passing it . For example :



The above can give rise to SQL Injection so use this way :




Hope you understand .


Save India From Corruption - Anna Hazare.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

How does that example demonstrate SQL injection? And how do you inject SQL via bind parameters?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
R. Grimes
Ranch Hand

Joined: Aug 23, 2009
Posts: 42
To Martin:

I'm curious as to if you have ever actually worked with a WAF, and know how powerful it is and what you can do with it?

Here's why I say the WAF is the best option:

1) In all cases, whether the server side program is checking for malicious parameters or not, the WAF is a front line of defense and will catch both the common attack methods as well as the many of the ones the programmer forgot to include in his code. Can you honestly say that, in your code reviews, every program is being carefully inspected to see if it checks the request for every possible SQL Injection attack method known? And, how do you handle it when a new attach mechanism comes out and you haven't included a check for it in all of your programs?

2) Many of the the things you would check, in your code, to evaluate the incoming request can be equally well defined at the WAF level through the parameter filtering rules and security policies. There is no difference in whether your Java code runs the checks against the incoming request, or whether you have defined the same request checking criteria in your WAF rules and policies. Request checking is request checking - regardless of where it happens. To say that it must occur at the server-side program level or it is not effective is ludicrous.

3) Companies, like Profense, are constantly keeping abreast of the latest techniques to attack a server. As they roll out updates to their software, you automatically gain that request checking intelligence on the front line of your defenses. This is a huge advantage in that you are covered immediately, rather than having to go back through all of your Java programs, and code in a check for this new attack mechanism. This would mean re-testing all your programs again. Good luck with that approach. Again, there is no advantage to having your Java program check, for example, the "duplicate parameter" type attack, when that is something easily detected by the WAF, and resolved at that level.

I think the strongest argument as to why the WAF is the most important ingredient is #3. When a new attack mechanism comes out, you'll be rushing to modify every single Java program to look for it, modifying the program, and then retesting them all. I'll define it once at the WAF level, and it will be blocked there for ALL programs.

Given your approach, I would assume you don't like AOP either. After all, why define an AOP pointcut and advice to enforce a new project wide policy change when you can just as easily go into every single program and code it multiple times?


Ron Grimes
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

I would probably second what Martin says.

WAFs may be good, but correctly written and tested software is better. And automatically testing such stuff is relatively painless.

What attack mechanisms are there that will get round the use of bind parameters?

R. Grimes
Ranch Hand

Joined: Aug 23, 2009
Posts: 42
I wonder if either of you two have ever actually worked extensively with a good WAF? I hope you guys are speaking from experience in having worked with both the server side programmatic approach, as well as the WAF security policy and rules approach, so that you can make a fair comparison. Or is this just a gut reaction?

I'm honestly curious - not trying to be provocative.

Ron
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Ron,

I think the problem lies in the definition of what "SQL injection" attack is. To the best of my understanding, the SQL injection attack is a situation where malicious user input changes the meaning of SQL query, by including SQL keywords that are processed by the database as such, instead of being processed as string literals.

This sort of attack is definitely and once for all prevented if you use binds, and for the rare occasions where it is not possible (eg. customizable ORDER BY clause) by something like using a hash map as suggested by Bill Karwin above. You don't have to engage in an arms race with hackers, you don't have to be vigilant against new types of SQL attacks. They cannot threaten you.

Unless, of course, you go the other path, that is not binding, but sanitizing (or not!) the inputs. In this situation, an ingenious hacker might well discover a new way to pass through the current tests, be it in your code or in your firewall, and you (or your firewall company) do have to recheck and retest with every new type of attack discovered.

I've been working with Oracle for years, and here if you don't bind you are not only prone to SQL injection attacks, but at the same time your application will not scale well at all and will consume lots of resources for parsing uselessly. Simply put, not to bind in Oracle is a serious bug. In other databases the performance penalty might not exist, but SQL injection prevention alone should be sufficient for anyone to bind everything that moves.

As I have noted in my post, the firewalls are indispensable. But to employ any firewall to prevent SQL injection, instead of fixing the application (or better, building it that way right from start) is a bad decision. I might build a CCTV system round my house, and I'd probably be safer. But it does not mean I should feel so secure not to lock the front doors.
R. Grimes
Ranch Hand

Joined: Aug 23, 2009
Posts: 42
I certainly use bind variable when possible. But, since I connect to a variety of databases (iSeries, Oracle, SQL 2000, MySQL), this is not always possible. Even with Oracle, I have had to form dynamic SQL statements because it complains I've tried to bind too many variables. Granted, this is the exception, but has occurred. And, with antiquated stuff like SQL 2000, the problem becomes even worse.

So, I like having a WAF that does a first check for SQL Injection in the request.

Ron
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41823
    
  63
Interesting discussion!

@Ron: Do you have an opinion on GreenSQL? Specifically, would it satisfy your needs in a DB firewall? I've been wondering about the specific circumstances where one might use it; opinions of any kind would be welcome.


Ping & DNS - my free Android networking tools app
R. Grimes
Ranch Hand

Joined: Aug 23, 2009
Posts: 42
@Ulf: I'm not knowledgeable enough about GreenSQL to have an opinion. I ended up recommending Profense to my company, and it was adopted, because it seemed to contain all the features of Imperva, but for a whole lot less money. It not only provides front line defenses against SQL Injection, but against all the OWASP top ten recommendations. So, unless GreenSQL addresses these top 10, I don't think it would do for us.
Ravi Kiran Va
Ranch Hand

Joined: Apr 18, 2009
Posts: 2234

How does that example demonstrate SQL injection? And how do you inject SQL via bind parameters?


I mean to say that check for any thing like 1='1' in username and password .
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Still don't follow how that demonstrates SQL injection.


I certainly use bind variable when possible. But, since I connect to a variety of databases (iSeries, Oracle, SQL 2000, MySQL), this is not always possible. Even with Oracle, I have had to form dynamic SQL statements because it complains I've tried to bind too many variables. Granted, this is the exception, but has occurred. And, with antiquated stuff like SQL 2000, the problem becomes even worse.


Well, SQL Server 2000 is long past it end of life. If you are working with legacy products I can see that using something like a WAF is a decent idea (though it will only protect transport it can interpret). I have to admit I work with quite a range of databases and I'm surprised you hit binding limits regularly enough to make it a concern. I still think that bind parameters are what people should aim for, particularly as it is fairly trivial to test the application's use of SQL. No need to rely on code reviews, a good automated tests harness and continuous integration will catch the places where developers have taken short cuts or made mistakes. I don't deny that WAFs have a purpose, and that they can perform this well. Writing your DAO layer expecting the presence of one though I still think is not the way I'd recommend going.
R. Grimes
Ranch Hand

Joined: Aug 23, 2009
Posts: 42
@Paul: I think this discussion has helped me refine my position a little better in my own mind. As such, I would restate it like this. I agree with the rest of you that variable binding is the smart way to go. I use it and always have, since I use Spring with JPA Eclipselink as the ORM. But, for input sanitizing, I believe that the WAF is the superior solution. Just as you would hope I'm not relying entirely on a WAF for protecting my database, I would also hope that you don't think you can do variable binding and your job is done in protecting your database.

Ron Grimes
Pat Farrell
Rancher

Joined: Aug 11, 2007
Posts: 4655
    
    5

Ravi Kiran Pattu wrote:I mean to say that check for any thing like 1='1' in username and password .

I'm not seeing this as causing problems. It would translate into something like

select fee, fie, foe, fum from masterusers where username=1='1'

How does this evaluate? does "username=true" have special meaning?
Abhishek Ralhan
Ranch Hand

Joined: Aug 01, 2010
Posts: 40

It is a common threat to security when you use SQL Injectoin in your code with using prepared statement.
Say you have an sql statement like

String s = "select col_user_salary from table_user where username =" + " '+ user_name +' ";
where user_name will be the value a user fills in a textbox.

Now, lets say user puts a value: xyz' or '1=1

and the query above will become:
select col_user_salary from table_user where username ='xyz' or '1=1' which evaluates to be true.

So the results will be displayed, whatever the value of user_name may be.


-Abhishek
I came to this world on a Learner's License
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

The above is only true if you do not use bind parameters. If you do this type of attack is not possible.
Abhishek Ralhan
Ranch Hand

Joined: Aug 01, 2010
Posts: 40

@Paul Sturrock:

Yes, that's what.
Actually, I was trying to explain this scenario to "Pat Farrell"
 
Don't get me started about those stupid light bulbs.
 
subject: how to prevent sql injection