Is there an equivalent of PHP's mysql_real_escape_string() for Perl's DBI?

Question

Could some tell me if there is a function which works the same as PHP's mysql_real_escape_string() for Perl from the DBI module?

Laurice

Answer 1

You should use placeholders and bind values.

Princess

DBH->bind() is the direct equivalent, but like Sinan says, don't do that. Do it properly with placeholders and bind values. – Darsie

Answer 2

Don't. Escape. SQL.

Don't. Quote. SQL.

Use SQL placeholders/parameters (?). The structure of the SQL statement and the data values represented by the placeholders are sent to the database completely separately, so (barring a bug in the database engine or the DBD module) there is absolutely no way that the data values can be interpreted as SQL commands.

my $name = "Robert'); DROP TABLE Students; --";
my $sth = $dbh->prepare('SELECT id, age FROM Students WHERE name = ?');
$sth->execute($name);  # Finds Little Bobby Tables without harming the db

As a side benefit, using placeholders is also more efficient if you re-use your SQL statement (it only needs to be prepared once) and no less efficient if you don't (if you don't call prepare explicitly, it still gets called implicitly before the query is executed).

Delldella

Placeholders can't be used for IN ('a', 'b', 'c') clauses where the values are a result of business logic. Here I need to escape the values myself. Would be fantastic to be able to write: ... where ID IN ?, ->execute(\@someIDs) but that is not possible... – Severe

@PeterV.Mørch True, you can't say ID IN ?, but you can say 'ID IN (' . join(',', ('?')x@someIDs) . ')' to dynamically create a query with the correct number of placeholders for your data. – Delldella

Answer 3

Like quote?

I would also recommend reading the documentation for DBD::MySQL if you are worried about utf8.

Messuage

Answer 4

From http://www.stonehenge.com/merlyn/UnixReview/col58.html :

  use SQL::Abstract;
  ... 
  my $sqa = SQL::Abstract->new;
  my ($owner, $account_type) = @_; # from inputs
  my ($sql, @bind) = $sqa->select('account_data', # table
                                  [qw(account_id balance)], # fields
                                  {
                                    account_owner => $owner,
                                    account_type => $account_type
                                  }, # "where"
                                 );
  my $sth = $dbh->prepare_cached($sql); # reuse SQL if we can
  $sth->execute(@bind); # execute it for this query

Cerebral

That's Randal Schwartz's article "Avoiding SQL Injection Attacks". Nice. – Messuage

Answer 5

Database Handle Method "quote"

my $dbh = DBI->connect( ... );
$sql = sprintf "SELECT foo FROM bar WHERE baz = %s",
          $dbh->quote("Don't");

http://metacpan.org/pod/DBI#quote

Kantar