SQL validation
I am writing web project.
There will be an input, where user can enter sql statement (only SELECT). I need to validate it.
One way is to make connection to DB, try to execute it and where exception will be thrown - display it on the jsp.
Is it correct way? Are there some java packeges for sql validation? Or may be u know some regular expressions for validation all possible SELECT statements(it may include inner join, left join, sub select , etc).
Thanks for help
[499 byte] By [
wariata] at [2007-11-27 8:50:02]
I can think of two approaches.
One, as you said, execute the SQL and catch the error message eg.
boolean sqlOkay = false;
String sqlError = null;
Statement statement = null;
try
{
Statement statement = connection.createStatement();
statement.executeQuery ( sql );
sqlOkay = true;
}
catch (SQLException sqlEx )
{
sqlError = sqlEx.getMessage();
}
finally
{
// clean up properly afterwards
try
{
if (statement != null )
statement.close();
}
catch (SQLException ignoreCloseErrors)
{
// do nothing
}
}
if (! sqlOkay )
{
// return the error message
}
the other way, would be to use an SQL parser.
However, this isn't foolproof.
There's no standard SQL dialect, unless you count Ansi 92 SQL.
For example, Oracle will allow a TO_DATE function, but DB2 would not.
So SQL parsing won't work in all cases, and will return errors where the SQL is actually valid in some databases.
I'd go with the first approach, and let the database driver do the work for you.
Alternatively, you could built up the select statement yourself.
Eg. show the user a list of tables, from the DatabaseMetaData class.
Then, show the user a list of fields from that table.
You simply create the select statement yourself then.
regards,
Owen
thanks for answer:)
I'd like to say, which kind of validation I need.
Example:
Select * from myTable;
I dont need to check - does the table 'myTable' exsists. I just want to know - is it correct statement. That's why I am looking for some regular expression that could match all possible SELECT statements, or something like regular expression (may be some java class).
And the second thing:
The app will be based on Struts 1.3 and pgSQL.
Thanks,
wariat
To make life simpler for yourself, you may have to impose rules on what SQL you allow.
Eg. only allow non-nested SQL, no unions nor intersects ie.
"SELECT <fieldlist> FROM <tablename>"
Otherwise, here's a few examples of valid SQL you'd have to allow for.
select * from customers where customer_type = 'B' OR balance < 0 AND role = 'Engineer'
select actn_key, actn_cd from ACTN where actn_key = 150
union
(
select actn_key, actn_cd from ACTN where actn_key = 152
)
select MODULE_ID, MODULE_NAME, MODULE_TYPE, MODULE_DESCRIPTION, MODULE_IMAGE_REF, MODULE_SEQ, MODULE_CLASS_REF, MODULE_TOOLTIP from
MODULES
where
MODULE_ID in ( select MODULE_ID from MODULE_MAP where MODULE_MAP_ID in ('a', 'b', 'c' ) or MODULE_TYPE = 'C'
order by MODULE_SEQ
