sybperl-l Archive

From: Hrvoje Niksic <hniksic at iskon dot hr>
Subject: Re: Arguments to SQL statements in Ctlib
Date: Nov 16 1999 10:24AM

Don Hosek  writes:

> I don't understand why you can't write
> 	@results = ct_sql("select foo from bar where id=$needed_it");

Here is a more verbose and hopefully more lucid explanation of why
that's generally not a good thing to do, borrowed from Mark-Jason
Dominus' _Short guide to DBI_.  The guide is linked from www.perl.com
title page.

    Don't do This
    =============

    People are always writing code like this: 

             while ($lastname = <>) {
               my $sth = $dbh->prepare("SELECT * FROM people 
                                        WHERE lastname = '$lastname'");
               $sth->execute();
               # and so on ...
             }

    Here we interpolated the value of $lastname directly into the SQL
    in the prepare call.

    This is a bad thing to do for three reasons.

    First, prepare calls can take a long time. The database server has
    to compile the SQL and figure out how it is going to run the
    query. If you have many similar queries, that is a waste of time.

    Second, it will not work if $lastname contains a name like
    O'Malley or D'Amico or some other name with an '. The ' has a
    special meaning in SQL, and the database will not understand when
    you ask it to prepare a statement that looks like

             SELECT * FROM people WHERE lastname = 'O'Malley'

    It will see that you have three 's and complain that you don't
    have a fourth matching ' somewhere else.

    Finally, if you're going to be constructing your query based on a
    user input, as we did in the example program, it's unsafe to
    simply interpolate the input directly into the query, because the
    user can construct a strange input in an attempt to trick your
    program into doing something it didn't expect. For example,
    suppose the user enters the following bizarre value for $input:

             x' or lastname = lastname or lastname = 'y

    Now our query has become something very surprising:

             SELECT * FROM people WHERE lastname = 'x' 
              or lastname = lastname or lastname = 'y'

    The part of this query that our sneaky user is interested in is
    the second or clause. This clause selects all the records for
    which lastname is equal to lastname; that is, all of them. We
    thought that the user was only going to be able to see a few
    records at a time, and now they've found a way to get them all at
    once. This probably wasn't what we wanted.

    People go to all sorts of trouble to get around these problems
    with interpolation. They write a function that puts the last name
    in quotes and then backslashes any apostrophes that appear in
    it. Then it breaks because they forgot to backslash
    backslashes. Then they make their escape function better. Then
    their code is a big message because they are calling the
    backslashing function every other line. They put a lot of work
    into it the backslashing function, and it was all for nothing,
    because the whole problem is solved by just putting a ? into the
    query, like this

             SELECT * FROM people WHERE lastname = ?

    All my examples look like this. It is safer and more convenient
    and more efficient to do it this way.