• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Dynamic SQL Injection Prevention.

 
clay jay
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all, I have a unique scenario which is making it difficult for me to figure out a proper way to prevent SQL injection.

A user has a text box in which they type the WHERE clause to a SQL query. I take that WHERE clause into a Servlet, validate it, execute it and display results back to the user. The problem is that they can use any number of parameters they want so I can't use Prepared Statements.

The only thing I can really think of is to escape/remove special characters although I feel doing it this way I won't be preventing everything possible so if anyone has a suggestion it would be much appreciated.
 
Lester Burnham
Rancher
Posts: 1337
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The problem is that they can use any number of parameters they want so I can't use Prepared Statements.

Why not?
 
clay jay
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Lester Burnham wrote:
The problem is that they can use any number of parameters they want so I can't use Prepared Statements.

Why not?


It's just one text box and if the user is entering the WHERE clause they can type:
aaa = 1 OR bbb = 2 AND ccc = 3
or maybe
aaa = 1 AND eee = 'asdf'
etc, whatever they want with any number of parameters they want and with any Logic they want

So unless I am completely missing something you are trying to point out to me, I don't see how it would be possible to use Prepared Statements in that scenario.
 
Paul Clapham
Sheriff
Posts: 21554
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What exactly do you mean by "parameters" in the context of that example? I don't see anything like parameters there.
 
clay jay
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:What exactly do you mean by "parameters" in the context of that example? I don't see anything like parameters there.


Sorry your right, I'm just not explaining it properly. The user isn't given a nice UI with possible parameters where they can enter values for the ones they like etc. All they are given is a text box and they are typing the entire WHERE clause basically any way they want. They could enter 'I like dogs' and my SQL statement would be something like:

which would result in an error of course. They could also enter aaa = 1 and bbb = 2 and ccc = 'asdffsd'; drop table table1--'

In which case I would like to prevent them from deleting my table. Unfortunately this is what I've been given to work with and unless it seems too costly to do it this way I don't think the business team will want to revamp the UI making it much easier and safer to work with. They want the full control of the WHERE clause without the extra time/work for the UI.
 
Paul Clapham
Sheriff
Posts: 21554
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's a good example. And I don't see any "special characters" in that example which you could look for. Maybe the semicolon, but people could use semicolons in perfectly reasonable queries too.

I don't think you can easily find SQL injection attacks in arbitrary SQL. You would really have to be able to parse the SQL and then reject SQL which tries to do things which you don't want it to do. You might be able to track down an SQL parser implementation, I suppose.

Although it might just be easier to make the database connection via a user profile which isn't allowed to modify the DB in any way. Then "drop table" would just throw an exception. If the users are only doing queries than that probably would be acceptable.
 
clay jay
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:That's a good example. And I don't see any "special characters" in that example which you could look for. Maybe the semicolon, but people could use semicolons in perfectly reasonable queries too.

I don't think you can easily find SQL injection attacks in arbitrary SQL. You would really have to be able to parse the SQL and then reject SQL which tries to do things which you don't want it to do. You might be able to track down an SQL parser implementation, I suppose.

Although it might just be easier to make the database connection via a user profile which isn't allowed to modify the DB in any way. Then "drop table" would just throw an exception. If the users are only doing queries than that probably would be acceptable.


I may be able to get by with setting up a read-only user but I know SQL injection can still be used to find information a user shouldn't be able to find with simple SELECTS so while it does prevent malicious attacks on the database it still might not be a satisfactory enough solution. I'll have to figure out if the data is too sensitive or not.

Unfortunately for that reason I think I might have to go the parser approach but I imagine I'd be able to find a good SQL parser somewhere.
 
Paul Clapham
Sheriff
Posts: 21554
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
clay jay wrote:I may be able to get by with setting up a read-only user but I know SQL injection can still be used to find information a user shouldn't be able to find with simple SELECTS so while it does prevent malicious attacks on the database it still might not be a satisfactory enough solution. I'll have to figure out if the data is too sensitive or not.


I hadn't thought of the possibility of intrusive access. Perhaps you could make other tables off-limits to your read-only user too? Anyway there's obviously a risk assessment process you're doing, should you involve the people who designed the form in that process?

There's also the possibility that a user could maliciously design a valid query which is so complex that it takes up too much of the database's resources...
 
clay jay
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:
clay jay wrote:I may be able to get by with setting up a read-only user but I know SQL injection can still be used to find information a user shouldn't be able to find with simple SELECTS so while it does prevent malicious attacks on the database it still might not be a satisfactory enough solution. I'll have to figure out if the data is too sensitive or not.


I hadn't thought of the possibility of intrusive access. Perhaps you could make other tables off-limits to your read-only user too? Anyway there's obviously a risk assessment process you're doing, should you involve the people who designed the form in that process?

There's also the possibility that a user could maliciously design a valid query which is so complex that it takes up too much of the database's resources...


Yes I also forgot about valid queries taking up all the database's resources....and there are probably more scenarios I'm not thinking of...Sigh...Such an annoying problem for a situation that really should be easy.

I think you are right, I'm going to have to sit down with the rest of the team and discuss this situation to see what is a satisfactory enough solution as it feels I will never get this totally secure at least without an enormous amount of work when it really shouldn't be very difficult to just change the UI so I can just validate parameters. I think it's just a matter of explaining to the business team the problem and hopefully they will give the okay to change the UI.

Thanks for the advice Paul.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic