Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

Re^2: Simple question on SQL Injection

by radix (Initiate)
on Oct 09, 2007 at 16:32 UTC ( #643738=note: print w/replies, xml ) Need Help??

in reply to Re: Simple question on SQL Injection
in thread Simple question on SQL Injection

Ah...I tried place holders but that did not work for me.. I had to use DataObject to use GetTbl etc as thats the only way I can get to the database. Is there anyother way I can use place holders if my D/B access look something like this: use DataObject; my $table = DataObject->GetTbl("table"); my $query = <<_QUERY_; select * from table where column is xyz _QUERY_ DataObject->SetDBIdent("MYDB"); my $results = DataObject->DoSql($query); Thanks

Replies are listed 'Best First'.
Re^3: Simple question on SQL Injection
by jhourcle (Prior) on Oct 09, 2007 at 16:56 UTC

    You can't vary the table name with placeholders, nor field names. They only work with values. However, if you're consistent in your naming, and only use a limited set of characters, you can test to see if input is safe, even if not valid. (for instance, only letters, digits, and underscores)

    warn "Invalid value" if ( $input =~ m/\W/ );

    note -- '\W' matches any character not matched by '\w', which matches letters, numbers, and underscore. The list of what qualifies as a 'letter' is dependant upon your locale settings. If you wanted only ascii letters, use the following:

    warn "Invalid value" if ( $input =~ m/[^a-zA-Z\d_]/ );
Re^3: Simple question on SQL Injection
by AK108 (Friar) on Oct 10, 2007 at 03:18 UTC
    You may be interested in SQL::Abstract. It allows you to build your query in Perl, and it returns the SQL along with the bind variables (for placeholders). A quick peek within the source revealed that it does quote table names and other values that you can't use placeholders for.

    However, I am not familiar with DataObject. If it takes regular SQL and supports placeholders, SQL::Abstract could still be helpful.

      A quick peek within the source revealed that it does quote table names and other values that you can't use placeholders for.
      SQL::Abstract does quote table names and field names with whatever $self->{quote_char} is set to, which is default to empty string. At least MySQL allows to quote table and field names with backtick characters (`table_name`).
      use SQL::Abstract; my $SQL = SQL::Abstract->new(quote_char => '`');
      Quoting tables and fields (selet * from `user`) is different from quoting values (where name = 'bob'). So what's the problem? You can't use placeholders on tables and fields, after all, only values. From DBI docs:
      With most drivers, placeholders canít be used for any element of a statement that would prevent the database server from validating the statement and creating a query execution plan for it. For example:
      "SELECT name, age FROM ?" # wrong (will probably fail) "SELECT name, ? FROM people" # wrong (but may not ífailí)

      Open source softwares? Share and enjoy. Make profit from them if you can. Yet, share and enjoy!

        I was unaware that SQL::Abstract did not quote table names. However, I am aware of the difference between placeholders and quoting (as I did a bit of pure DBI before using SQL::Abstract in conjunction with it). Your message does show the difference better than mine.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://643738]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (8)
As of 2019-09-18 07:31 GMT
Find Nodes?
    Voting Booth?
    The room is dark, and your next move is ...

    Results (223 votes). Check out past polls.