Re: escape single quote in INSERT command

Hunter wrote:
> Hi Group -
>
> I have a perl application for a registration form. I'd like to put
> escape characters in my insert command to accommodate for '
> (i.e. O'Brien, O'Malley, etc). I've tired double quotes, single
> quotes, back tick, forward ticks, curly bracket, round brackets - no
> success.

The SQL standard in literals is to quote with another single quote (like
'O''Brien'). Not sure why that didn't work for you. But PostgreSQL also
escapes with backslashes ('O\'Brien'), so they also must be escaped
('c:\\winnt'). SQL-92 doesn't. So there's no database-independent way to
quote things 100% correctly.

So don't quote it manually. Either do (preferred)

my $sth = $dbh->prepare(qq{insert into mytable values (?, ?, ?)});
$sth->execute($a, $b, $c);

or

$dbh->do(sprintf("insert into mytable values (%s, %s, %s)",
$dbh->quote($a), $dbh->quote($b),
$dbh->quote($c)));

The first way is better because:

- you don't have to explicitly think about quoting; it takes care of it
- it can use server-side prepared statements for more goodness

The second way, though, at least quotes things correctly. You need to
use $dbh->quote() to do that because quoting is database-specific, as I
mentioned above.

Both ways map Perl undef -> null for you.

Scott