SQL statement validation in input box

[PrimeMinister]

Hey guys, how would I check that a SQL statement is correctly entered into a input box using code? (I'm doing this in delphi, so help in delphi would be most helpful. Otherwise just explain what you would do in any other language pls.)

Thx in advance!

[mariushm]

I guess it depends on the target ... MS SQL will be different than MySQL SQL which will be different than Postgress SQL or SQLite SQL.

 

There's no ready made solution that's free... you would probably have to write your own sql parser and checker... the only other solution that comes to mind is creating a database in memory and then running the query from that input box directly in that memory database and see what the database engine says, what error message it returns.

If user enters something like "select * from products limit 100 "  the database engine will complain "products doesn't exist in [memory database]" but at least that will tell you that the query was parsed correctly.

[Eigenvektor]

As @mariushm said, you'd have to write your own parser/lexer and so on. However, if the statement it sent against a database anyway, just let the database do that for you.

 

As an aside: If you do run stuff against a database, and this is for some production system, it's never a good idea to let people enter SQL statements on their own. If this is just for validating statements, learning how to write your own parser might be a fun challenge.

[JacobFW]

I 2nd what @Eigenvektor says.  Letting people enter their own SQL queries is ripe for abuse.  You'd be much better off writing an intermediary system where you take values in from a user, and then translate them into the resulting query (DO NOT FORGET TO USE QUERY PARAMETERS).

 

If however you are a glutton for punishment and putting you're entire database at risk and want to allow users to enter a query, then you're still in for a challenge.  While most queries seem simple, they can get incredibly complex.  Even if you did go through the hassle of writing your own parser and validator, it still might be in vain, as databases do get updates, new functions, etc, added.  Not to mention you can have custom procedures you or someone else has written for your database.

 

What you really need is for the database manager itself to validate your query, and unfortunately, not a single database manager I'm aware of offers this as a feature.

You may be able to simulate this yourself though.  If you create a database login with no permissions, you may be able to determine if the query is valid or not, by running it under that user account, and looking at the resulting error message.  Many databases will parse the query, see what permissions are needed for that query, check against the user's permissions, and if they don't match, say permission denied.  So if you run a query and get a "permission denied" error, then more than likely the query was valid.  If you run the query and get a syntax error, then it isn't.

 

Again, allow me to repeat I really don't recommend you take this approach.  It'd be very, very easy to make a mistake and open yourself up to a security nightmare.  Not to mention it might not reliably work.  Even if that approach works now, it could very well be that in the interest of improving performance, the developers decide to have the database manager check the user permissions before it parses the query, to make sure the user has at least some permissions to make some action, before it needlessly wastes time parsing the query.

 

 

[PrimeMinister]

On 8/16/2020 at 10:39 AM, mariushm said:

I guess it depends on the target ... MS SQL will be different than MySQL SQL which will be different than Postgress SQL or SQLite SQL.

 

There's no ready made solution that's free... you would probably have to write your own sql parser and checker... the only other solution that comes to mind is creating a database in memory and then running the query from that input box directly in that memory database and see what the database engine says, what error message it returns.

If user enters something like "select * from products limit 100 "  the database engine will complain "products doesn't exist in [memory database]" but at least that will tell you that the query was parsed correctly.

 

On 8/16/2020 at 9:21 PM, Eigenvektor said:

As @mariushm said, you'd have to write your own parser/lexer and so on. However, if the statement it sent against a database anyway, just let the database do that for you.

 

As an aside: If you do run stuff against a database, and this is for some production system, it's never a good idea to let people enter SQL statements on their own. If this is just for validating statements, learning how to write your own parser might be a fun challenge.

 

On 8/17/2020 at 4:39 PM, JacobFW said:

I 2nd what @Eigenvektor says.  Letting people enter their own SQL queries is ripe for abuse.  You'd be much better off writing an intermediary system where you take values in from a user, and then translate them into the resulting query (DO NOT FORGET TO USE QUERY PARAMETERS).

 

If however you are a glutton for punishment and putting you're entire database at risk and want to allow users to enter a query, then you're still in for a challenge.  While most queries seem simple, they can get incredibly complex.  Even if you did go through the hassle of writing your own parser and validator, it still might be in vain, as databases do get updates, new functions, etc, added.  Not to mention you can have custom procedures you or someone else has written for your database.

 

What you really need is for the database manager itself to validate your query, and unfortunately, not a single database manager I'm aware of offers this as a feature.

You may be able to simulate this yourself though.  If you create a database login with no permissions, you may be able to determine if the query is valid or not, by running it under that user account, and looking at the resulting error message.  Many databases will parse the query, see what permissions are needed for that query, check against the user's permissions, and if they don't match, say permission denied.  So if you run a query and get a "permission denied" error, then more than likely the query was valid.  If you run the query and get a syntax error, then it isn't.

 

Again, allow me to repeat I really don't recommend you take this approach.  It'd be very, very easy to make a mistake and open yourself up to a security nightmare.  Not to mention it might not reliably work.  Even if that approach works now, it could very well be that in the interest of improving performance, the developers decide to have the database manager check the user permissions before it parses the query, to make sure the user has at least some permissions to make some action, before it needlessly wastes time parsing the query.

 

 

Thanks to all of you that replied and gave some advice! I have decided not to do the SQL validation thing for my project cause I'm too time-constrained for something that might take too long. But maybe in future I'll make a validation tool for it. Thx again for the replies!