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

DWS mentioned: "If your script is for general consumption, I hope you're using -T and are untainting $SQLCommand. Otherwise, someone can slip something truly evil into your SQL stream."

It was a month ago and I forgto to follow up with him.

I would love to learn more about using-T and untainted SQl commands. Security is integral. I want to do things right. Can some one steer me in the right direction?

Thanks
Simon

Replies are listed 'Best First'.
Re: Using-T and Untainting SQL
by lachoy (Parson) on Apr 29, 2002 at 15:58 UTC

    In addition to running under -T, you probably want to investigate the Taint attribute of a DBI handle. It's well-documented in the DBI perldoc, but briefly: if -T is on and the Taint attibute of a DBI handle is true, then all data fetched from the database are tainted, and information processed by DBI via method calls are checked for taintedness.

    Chris
    M-x auto-bs-mode

[reply]
[d/l]
[select]
Re: Using-T and Untainting SQL
by tomhukins (Curate) on Apr 29, 2002 at 15:55 UTC
    If you want to pass potentially insecure variables to SQL commands using DBI, placeholders are arguably the best choice. If you can't or don't want to use placeholders (some DBD drivers do not support them), read up on DBI's quote method.
[reply]
[d/l]
Re: Using-T and Untainting SQL
by dws (Chancellor) on Apr 29, 2002 at 16:56 UTC
    If memory serves, what I meant was that any time you're building an SQL query from parts passed in via form, you have to be very, very careful to prevent someone from sneaking in something tricky. If you're just passing in values, using binding makes things safe. But if you're passing in any other type of fragment, test it carefully to verify that it's what you expect.

    The risk you run by doing my $SQL = "SELECT * FROM table WHERE " . param('whereClause'); is that someone will pass in   0; drop table; And *poof*, you're out of business.

[reply]
[d/l]
[select]

      So using placeholders prevents this from happening? The data is still sent to the db although not through the use of placeholders, but through other variables, cannot the hacker still intercept the information? I think this is where T comes in. I am looking into this right now.

      ues I am trying to locate good documentation on the issue... nothing like a good technical doc to sink my teeth into.

      Thanks
      Simon
[reply]
        Take this as an example: 
        my $username = $query->('username');
# Do some input validation if necessary

# DBI code
my $sql = "SELECT * FROM users WHERE username = ?";
...
$sth->execute($username);

        [download]
        (Note: There are other ways of specifying values for placeholders and binding values, as it is referred to in the DBI documentation.)

        If a mailicious user were to pass in PotPieMan; DROP TABLE users for the username, the DBI module would parse this as the following: SELECT * FROM users WHERE username = 'PotPieMan; DROP TABLE users';

        and (most likely) return 0 rows. The point is that you, the programmer, have to worry A LOT LESS about getting every posssible case of SQL exploitation covered.

        --PotPieMan

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom