Programming the Perl DBI

Database-Specific Quote Handling

By far the most important utility method is quote() , which correctly quotes and escapes SQL statements in a way that is suitable for a given database engine. This feature is important if you have a Perl string that you wish to insert into a database, as the data will be required, in most cases, to have quotation marks around it.

To confuse matters, database engines tend to have a different format for specifying these surrounding quotation marks. DBI circumvents this problem by declaring the quote() method to be executed against a database handle, which ensures that the correct quotation rules are applied.

This method, when executed against a database handle, converts the string given as an argument according to defined rules, and returns the correctly escaped string for use against the database.

For example:

#!/usr/bin/perl -w
#
# ch04/util/quote1: Demonstrates the use of the $dbh->quote() method

use DBI;

### The string to quote
my $string = "Don't view in monochrome (it looks 'fuzzy')!";

### Connect to the database
my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username", "password" , {
    RaiseError => 1
} );

### Escape the string quotes ...
my $quotedString = $dbh->quote( $string );

### Use quoted string as a string literal in a SQL statement
my $sth = $dbh->prepare( "
    SELECT *
    FROM media
    WHERE description = $quotedString
  " );
$sth->execute();

exit;

For example, if you quoted the Perl string of Do it! via an Oracle database handle, you would be returned ...