http://www.perlmonks.org?node_id=643730

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

I am a perl beginer.. All I am trying to do is -Accept and argument and pass that argument into an sql query. But I have to validate the input before I pass the argument to the sql code. For ex- if I pass the argument as TE\'ST and my query is select * from table where entry is TE\'ST and that is not good..so how can I write a validation statement to detect SQL injection. Thanks in advance

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

    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.

      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

        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_]/ );
        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.

Re: Simple question on SQL Injection
by mwah (Hermit) on Oct 09, 2007 at 16:31 UTC
    radixhow can I write a validation statement to detect SQL injection

    1) the canonical way to prevent SQL injection is, like ikegami and
    jhourcle have pointed out already, to use placeholders (they are
    even "native" in mysql-5).

    2) you can use the the "quote" method from DBI, like:
    ... $dbh = DBI->connect(...); $sql = sprintf "SELECT name FROM users WHERE name=%s AND passwd=%s", $dbh->quote($bad_name), $dbh->quote($bad_pass); ...
    For detection, you could just count the occurences of the \' char somewhere
    in the input (or whatever quote the DB uses). That would be almost the only
    danger (IIRC)

    Regards

    mwa
      The canonical way to prevent SQL injection is to use placeholders.
      An alternative approach is to allow access to data through stored procedures only, and disallow dynamic sql altogether. Such an approach has a number of interesting benefits, including performance and security.
      --
      Andreas
      Sorry to hijack the thread, but I have been reading this one with interest. I am using DBI and mysql and want to use a blob to store binary files. I cannot find much information about inserts, blobs, and sql injection. Is it okay to use placeholders with blobs? Will it all "just work?" Do I have to do anything else? Will Britney be okay? I gotta know!

      Thanks in advance and sorry to carry on...

      Thanks mwa, But I cant use DBI, I can only use DataObject..is there anyway I can use palce holders with DataObject? Thanks
        I don't see any DataObject module on CPAN (except for something under Spoon, but I don't think that's it). We can't help you with modules that we know nothing about. Is DBI installed? You might at least be able to call the quote() method from it on your parameters. Or see if your DataObject module can handle placeholders or has something like the quote() method.
        Too bad you can't use DBI and I don't know what the heck DataObject is, but I hope it uses DBI underneath. Using -T switch could help in conjunction with Taint option of DBI (if, again, you use DBI). Use regex to validate user inputs, but you need to define what constitutes bad or good input. For example, to allow only alphanumeric character,
        my $user = get_username(); if ($user =~ /^(\w+)$/) { $user = $1; } else { die "Hey, you gave me bad input: $user\n"; } # proceed with untainted $user

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

Re: Simple question on SQL Injection
by ikegami (Pope) on Oct 09, 2007 at 16:09 UTC
    Use replaceable parameters (avoiding the need for validation and quoting).
Re: Simple question on SQL Injection
by andreas1234567 (Vicar) on Oct 10, 2007 at 07:47 UTC
    An anecdotal cartoon on what happens if you fail to sanitize SQL input:

    Exploits of a Mom (xkcd.com). I can't help but smile at

    - Did you really name your son "Robert'); DROP TABLE Student;--" ?
    - Oh, yes. Little Bobby Tables we call him.
    --
    Andreas