Errto has asked for the wisdom of the Perl Monks concerning the following question:

I have an application that allows users to type in SQL statement that my application will then store for later use. I would like to make sure, at time of submission, that the user has put in a valid SQL statement for the particular database that they are planning to run it against. But I would prefer not to actually execute the statement, especially if it is an UPDATE or INSERT, which it could be. I'm aware of SQL::Statement, but I need a few things it doesn't provide:

In other words, I need to be able to do this through DBI itself so that the actual database will tell me that my statement is or isn't valid given the username/schema I'm accessing.

What methods are there through DBI or some other module that might allow this? What about DB-specific methods if there are no generic ones (my main targets are Oracle, DB2 and MySQL)? Thanks

Replies are listed 'Best First'.
Re: DBI - validate SQL without executing
by ptum (Priest) on Apr 10, 2006 at 17:58 UTC

    What about creating a statement handle on the statement using prepare? Couldn't you check DBI::errstr on a prepare to determine if the statement was valid?

    Maybe something like this simple example ... 

    #!/usr/local/bin/perl
use warnings;
use strict;
use DBI;
require '/up/web/standard.pl';

my $dbh = db_connect_local(); # my home-grown connection routine
my $statement = 'select blah from my_table'; # blah is not a valid col
+umn on that table.

my $sth = $dbh->prepare($statement);
unless ($sth) { 
  print "Error: $DBI::errstr\n";
}

    [download]

    Update: It seems this does not work for MySQL, but it does work under DBD::Oracle. Thanks to erix and tye for pointing that out.


    No good deed goes unpunished. -- (attributed to) Oscar Wilde
[reply]
[d/l]
Re: DBI - validate SQL without executing
by jZed (Prior) on Apr 10, 2006 at 18:47 UTC
    If there's any way to use your RDBMS directly (e.g. the other suggestions to prepare but not execute or to add a WHERE 1=0), that would be your best bet. If that won't work, SQL::Statement can be tweaked to do some DBMS-specific work. It won't handle things like CASE statements and subqueries (other than those it supports already) since those invovle a different level of parsing.

    But you can teach it about DBMS specific data types, functions, and operators with the CREATE and DROP commands for types, functions and operators. For example if your DBMS doesn't support BLOB as a data type, issuing $dbh->do("DROP TYPE blob") before you begin will cause it to reject the word BLOB when used as a data type. "CREATE TYPE foo" will do the reverse - accept "foo" as a data type. The same works with functions and some operators (e.g. SLIKE or RLIKE).

    The other thing you can do is use S::S as a first pass validator - in other words, you could use it to filter out all of the statements it recognizes as valid and then the ones that are left over you'd need to deal with some other way.

    Let me know if you need more specifc tips.
[reply]
Re: DBI - validate SQL without executing
by erix (Prior) on Apr 10, 2006 at 17:07 UTC

    How about just adding a '0 = 1' condition to the statement?

    I'd prefer to build the sql from separate parts (column-list, table list, join-list, condition-list, with only columns and conditions user-defined), but that would obviously be quite another approach, and more work.

[reply]
Re: DBI - validate SQL without executing
by davidrw (Prior) on Apr 10, 2006 at 16:38 UTC
    security issues aside about running arbitrary strings from users, you could just do something like (you probably want some kind of injection-projection against ";commit" or something) just run it, check result/error, and rollback .. Assumes of course that the db supports transactions...
[reply]
      That is the approach that I was using, until someone pointed out that at least with some versions/database types of MySQL, that fails precisely because of the transaction issue. That was one motivator for my question; the other was that some of the statements can be significant resource hogs, so it would be better to hold off executing them until they're actually needed.
[reply]

Back to Seekers of Perl Wisdom