How can I correctly insert data containing special characters into a database field using Perl and DBI?

Question

I have a form, not unlike the post question/comment on this site that I want to post to a field in a database.

However if someone where to put special characters such as @#;"| either fails or does not insert correctly. Is there a way to insert said data into a database without Perl trying to treat certain characters as operators?

Answer 1

Try the quote method of database handle:

quote

  $sql = $dbh->quote($value);
  $sql = $dbh->quote($value, $data_type);

Quote a string literal for use as a literal value in an SQL statement, by escaping any special characters (such as quotation marks) contained within the string and adding the required type of outer quotation marks.

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

A better practice is to use placeholders and bind values, though.