There may not be a fully portable answer to this. My best guess for a concise answer would be:     "... LIKE '%" . quotemeta($str) . "%' ..." but I fear that some database won't handle all of the potential backslashes. So a more conservative approach might be called for: 

    my $pattern= $string_from_user;
    $pattern =~ s#(['%_\[\]])#\\$1#g;
    "... LIKE '%$pattern%' ..."

[download]
but then some databases might support things other than %, _, and [chars] in their LIKE constructs so the first answer might be better.

Do all databases even support \ for escaping LIKE "wildcards"? What about for escaping single quotes (apostrophes)? I believe that using two consecutive apostrophes to represent a single apostrophe when inside of an apostrophe-delimited string is part of the SQL standard. So 'don''t' = "don't" in SQL. I've long been curious what the standard says about LIKE strings but have always ended up looking up what my current database supports instead (which has always been the use of \ to escape things).

In dealing with MySQL for PerlMonks, I found what I consider to be a quirk. If I want to find all strings containing a backslash followed by a percent sign (\%), I have to tell MySQL     LIKE '%\\\\\%%' (which means I'd have to tell Perl "... LIKE '%\\\\\\\\\\%%' ...") because, when I use what I would have expected to work (LIKE '%\\\%%'), it appears that some backslahses are consumed when MySQL parses the single-quoted string and the LIKE operation ends up seeing %\\%%, which finds strings that contain just a backslash.

So you might have to resort to: 

    # Escape "wildcard" characters in user input:
    my $pattern= quotemeta($string_from_user);
    # Escape things processed by 'single quote' parsing:
    $pattern =~ s#([\\'])#\\$1#g;
    "... LIKE '%$pattern%' ..."

[download]
So I fear you'll have to test and use what works for your current database. This should probably be abstracted by DBI but I don't believe that it currently is.

        - tye

In reply to Re: DBI, quoting, and LIKE searches (no good answer) by tye
in thread DBI, quoting, and LIKE searches by Mr_Person

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.