Using regex to add a single backslash to escape quotes, instead of 2 backslashes or 0

lancer has asked for the wisdom of the Perl Monks concerning the following question:

Dear All,

I have a function like

sub sql_escape 
{
    my $text = shift;
    $text =~ s/'/\'/g;
    $text =~ s/"/\"/g;
    $text =~ s/\\/\\\\/g;
    return $text;
}
[download]

Outputs this: O'REILLY

When I print the result of that in a double quoted string, like so: my $escaped_text = sql_escape ($raw_text); print "$escaped_text" , it prints single quotes without the backslash.

But if I change the function to add another backslash, it starts printing 2 backslashes:

sub sql_escape 
{
    my $text = shift;
    $text =~ s/'/\\'/g;
    $text =~ s/"/\\"/g;
    $text =~ s/\\/\\\\/g;
    return $text;
}
[download]

Outputs this: O\\'REILLY

How can I change it to output only 1 backslash?

Comment on Using regex to add a single backslash to escape quotes, instead of 2 backslashes or 0 Select or Download Code

Replies are listed 'Best First'.
Re: Using regex to add a single backslash to escape quotes, instead of 2 backslashes or 0 by Corion (Patriarch) on Jul 26, 2012 at 17:02 UTC
See the `->quote` method of DBI. Do not implement this routine yourself, and even better, use DBI placeholders instead, also documented in DBI.	[reply] [d/l]
Re^2: Using regex to add a single backslash to escape quotes, instead of 2 backslashes or 0 by lancer (Scribe) on Jul 26, 2012 at 17:50 UTC
Thanks for the suggestion, I was able to replace it with this: `sub sql_escape { my $text = shift; $text = DBD::_::db->quote ($text); return $text; }` [download] Outputs: O''REILLY (This format works with MySQL)	[reply] [d/l]
Re^3: Using regex to add a single backslash to escape quotes, instead of 2 backslashes or 0 by Corion (Patriarch) on Jul 26, 2012 at 18:26 UTC
Why don't you use `$dbh->quote(...)` [download] as the documentation suggests?	[reply] [d/l]
Re^4: Using regex to add a single backslash to escape quotes, instead of 2 backslashes or 0 by lancer (Scribe) on Jul 26, 2012 at 21:39 UTC
Re: Using regex to add a single backslash to escape quotes, instead of 2 backslashes or 0 by BillKSmith (Monsignor) on Jul 26, 2012 at 18:43 UTC
Just for fun, here is a fix to your function. `use strict; use warnings; my $message = q(O'REILLY); my $sql_message = sql_escape( $message); print "$sql_message"; sub sql_escape { my $text = shift; $text =~ s/'/\\'/g; $text =~ s/"/\\"/g; return $text; }` [download] Use the module!	[reply] [d/l]
Re^2: Using regex to add a single backslash to escape quotes, instead of 2 backslashes or 0 by wink (Scribe) on Jul 26, 2012 at 21:17 UTC
For completeness, here's WHY this fixes your function so you can maybe avoid similar issues in the future. `$text = "O'REILLY"; $text =~ s/'/\\'/g; # $text is now O\'REILLY $text =~ s/"/\\"/g; # Unchanged $text =~ s/\\/\\\\/g; #$text is not O\\'REILLY` [download] So you properly replace it with a single backslash, but then you tell it to replace all single backslashes with double backslashes. If you wanted to do this properly, move the 3rd line above the 1st. But everyone is right, use the module.	[reply] [d/l]
Re^3: Using regex to add a single backslash to escape quotes, instead of 2 backslashes or 0 by lancer (Scribe) on Jul 27, 2012 at 08:00 UTC
Wow, thanks for that. Such a basic mistake. :)	[reply]
Re: Using regex to add a single backslash to escape quotes, instead of 2 backslashes or 0 by Anonymous Monk on Jul 26, 2012 at 17:28 UTC
Pay attention to what Corion has suggested. By implementing this yourself, you are very likely to miss edge cases and grey areas that the DBI implementation has already addressed.	[reply]
Re^2: Using regex to add a single backslash to escape quotes, instead of 2 backslashes or 0 by lancer (Scribe) on Jul 26, 2012 at 17:54 UTC
Thanks Anonymous Monk, I've used Corion's suggestion. I posted it above.	[reply]