in reply to Re: Quick 'Quote' DBI Question
in thread Quick 'Quote' DBI Question

Please use Herkum's method - use placeholders.

As a short introduction, placeholders mean you put a question mark anywhere you'd put (quoted or unquoted) input when you prepare() your sql query, and then supply the values when you execute() the query: 

my $sth = $dbh->prepare("SELECT something FROM something_else WHERE co
+l1 = ? OR col2 = ?");

$sth->execute($value1,$value2);

while (my ($result) = $sth->fetchrow) {
  # do something with $result
}

[download]
Using placeholder guarantees your values will always be "quoted" correctly, regardless of what's in them.
"What should it profit a man, if he should win a flame war, yet lose his cool?"

Replies are listed 'Best First'.
Re^3: Quick 'Quote' DBI Question
by Trihedralguy (Pilgrim) on Apr 06, 2007 at 14:07 UTC
    What do you mean by $results, where does this value come from? I guess I don't really understand when using placeholders, how do I do a $fetchrow on the values that have already ben executed?
[reply]
      I'm using fetchrow() as a shorthand of fetchrow_array(). I'm pretty sure that's normal usage, but I can't find it documented anywhere. So I'll use fetchrow_array() from here on.

      In my example, $result is simply the return value of fetchrow_array() - I.e. a single row of data with a single column. Note that a single query can return multiple rows of results: 

      while (my ($col1,$col2) = $sth->fetchrow_array()) {
  # ...
}

      [download]
      Every iteration of that while loop sets $col1 and $col2 to the first and second columns of the next result row.

      Using placeholders doesn't have any direct influence on how you fetch the results. It only influences how you prepare and execute the query before reading the results.

      Note that using placeholders like this splits up the process in three distinct steps:

      1. preparing the query (with the question marks as placeholder marking input values). - this gives you a statement handle.
      2. executing the query from the statement handle (where you need to supply the values for each place-holder)
      3. fetching the results of the executed query from the statement handle.
      That means you can also prepare a statement only once and then re-execute it with different values (provided you save the statement-handle somewhere - or use the prepare_cached() method). This can speed up querying since the database then doesn't have to parse your query each time - but if it does depends on the implementation.

      "What should it profit a man, if he should win a flame war, yet lose his cool?"
[reply]
[d/l]
        I think I understand this a lot better now, thank you.
        I have one more related quick question.
        In my first statement the where on SSN has a % sign on it, because the user only enters the last 4 digits, how do I do this with placeholders?
        The original line was:and SSN like '%$ssn'
        Here is my new SQL with placeholders:
        my $sql = <<"END"
SELECT
  SSN, RTRIM(LAST_NAME,' '),
  TO_CHAR(BIRTH_DATE,'YYYY'),
  STORE
FROM
  PYHADMIN.PY_EMP_SHADOW
where
      TO_CHAR(BIRTH_DATE,'YYYY') like ?
  and SSN like ?
  and RTRIM(LAST_NAME,'  ') like upper(?)
  and rownum <= 1
END
;
my $yi = $dbh->prepare($sql);
$yi->execute($year, $ssn, $name);
#--------Send the values from the query into variables
($SSNc, $LAST_NAMEc, $YEARc, $storenum) = $yi->fetchrow_array;

        [download]
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom