Is there an equivalent of PHP's mysql_real_esc

问题:

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?

回答1:

You should use placeholders and bind values.

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

回答3:

Like quote?

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

回答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

回答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