in reply to Simple question on SQL Injection

No need to test, just use placeholders.

Okay, technically, this only prevents it from being real problem, and it won't allow you pass to some alternate logic (eg, blacklist the incoming IP) when you see an injection attempt. The problem is that detecting abuse it like trying to detect spam e-mail -- there are some that are obvious, and some that might be acceptable, but you need to know the context.

For instance, it's much easier to locate bad values in a numeric field, or if there should have only been a limited set of choices to validate against. If it's freeform text (eg, passwords), or even worse -- binary data into a blob (eg, an image file), you might not be able to validate it simply on what characters are present, but have to look for patterns, and even then, you might have false positives.

Replies are listed 'Best First'.
Re^2: Simple question on SQL Injection
by radix (Initiate) on Oct 09, 2007 at 16:32 UTC
    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
[reply]

      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_]/ );
[reply]
[d/l]
[select]
      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.

[reply]
        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 => '`');

        [download]
        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’)

        [download]

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

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom