9

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?

Sinan Ünür
  • 116,958
  • 15
  • 196
  • 339
Phil Jackson
  • 10,238
  • 23
  • 96
  • 130

5 Answers5

16

You should use placeholders and bind values.

Sinan Ünür
  • 116,958
  • 15
  • 196
  • 339
  • 1
    `DBH->bind()` is the direct equivalent, but like Sinan says, don't do that. Do it properly with placeholders and bind values. – mpeters Feb 05 '10 at 16:35
9

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).

Dave Sherohman
  • 45,363
  • 14
  • 64
  • 102
  • 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... – Peter V. Mørch Apr 19 '18 at 14:14
  • @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. – Dave Sherohman Apr 20 '18 at 07:49
5

Like quote?

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

Sinan Ünür
  • 116,958
  • 15
  • 196
  • 339
Christopher Bottoms
  • 11,218
  • 8
  • 50
  • 99
2

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
atk
  • 9,244
  • 3
  • 32
  • 32
0

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

WreWolf
  • 121
  • 3