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

Hi, I'm trying to "escape" quotes in a query string for a mySQL query (its an admin feature, so I don't need to worry too much about SQL injection, as its only accessable from our IP's)

The problem I'm having, is with this code:

$in->{client_detail_admin_notes_search} =~ s/"/\"/g;

What I need to do, is convert (for example):

foo "bar" search

..to:

foo \"bar\" search

..otherwise the query:

push @select_conds, qq|client_detail_admin_notes LIKE "%$in->{client_detail_admin_notes_search}%"|;

will break, as it would convert to:

client_detail_admin_notes LIKE "%foo "bar" search%"

I've tried doing \\\ and \\, but that never gives what I need. For example, if I use:

$in->{client_detail_admin_notes_search} =~ s/"/\\"/g;

..it translates to this in @select_conds:

$VAR1 = 'client_detail_admin_notes LIKE "%\\"quotes\\"%"';

Which obviously isn't right :(

Can anyone suggest a way around this? Been driving me up the wall!

TIA

Andy

Replies are listed 'Best First'.
Re: Escaping quotes - can't quite get it!
by Corion (Patriarch) on May 18, 2011 at 08:51 UTC

    SQL uses single quotes, so quote your LIKE expression using single quotes as well.

    I recommend you use placeholders instead of interpolating things. It relieves you of the headache of having to properly quote stuff.

[reply]
[d/l]
      Hi,

      Thanks for the reply. Unfortunatly, we can't use placeholders, as its using an external SQL module to process the queries. I think I'm just going to settle for removing the " and replacing them with %

      Is there a way of using % in a way, so it ONLY matches one charachter? Kinda like we do in a regex with . ?

      TIA!

      Andy
[reply]

        Maybe now is a good time to learn about SQL?

        Depending on your SQL dialect, the "one character" (or "one byte", depending on your database/data structure) wildcard is _ or ?. As you seem to be successfully using % as wildcard, it is more likely _ than ?.

[reply]
[d/l]
[select]

        Personally, a lack-of-support for placeholders would be a deal-breaker. It'd be sufficient, on its own, to warrant the dumping of that module and the search for a different module, or the ground-up writing of a new one, if need be. Injection attacks are not the only problem solved by placeholders, though, again, all on its own, it's sufficient cause for any database-interfacing module to support them. If the author of the module is not aware enough of this to simply allow binding of variables through their interface into DBI, then I would be concerned about everything else. (Which, of course, is not to say that allowing placeholders would be sufficient to draw the conclusion that they're a DB expert.)

[reply]
        Never mind - I think I answered my own question, with the REGEXP function in mySQL :)

        Cheers

        Andy
[reply]
Re: Escaping quotes - can't quite get it!
by ikegami (Patriarch) on May 18, 2011 at 09:10 UTC 
    "... LIKE " . $dbh->quote("%$in->{client_detail_admin_notes_search}%")

    [download]
[reply]
[d/l]
Re: Escaping quotes - can't quite get it!
by thewebsi (Scribe) on May 18, 2011 at 09:09 UTC

    $VAR1 = 'client_detail_admin_notes LIKE "%\\"quotes\\"%"';

    Which obviously isn't right :(

    Actually I think that is what you want. Remember that Data::Dumper doesn't exactly print the actual content of the variable, rather it prints code that may be used to instantiate it. So if you run:

    $VAR1 = 'client_detail_admin_notes LIKE "%\\"quotes\\"%"';
print $VAR1;

    [download]

    you get:

    client_detail_admin_notes LIKE "%\"quotes\"%"

    as desired.

    Arnon Weinberg Back2Front - The Web Site People
[reply]
[d/l]
[select]
      Yeah you're right - thats what I realized about 5 minutes ago (when printing out the actual string that was being passed to mySQL)

      Thanks for the pointer though :)
[reply]

Back to Seekers of Perl Wisdom