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

Dear Monks--

I'm trying to write a full text search using placeholders.

The general format is:
my $sql = qq{SELECT * FROM $table WHERE (MATCH(Author) AGAINST('+herma
+n +melville' IN BOOLEAN MODE)) order by Title};

[download]
But I'd like to use placeholders. I've tried: 
my $sql = qq{SELECT * FROM $table WHERE (MATCH(Author) AGAINST('? ?' I
+N BOOLEAN MODE)) order by Title};

$ding -> execute("+herman", "+melville") or   die   DBI->errstr;

[download]
But I get an error message saying two values called when none are needed. (I've tried escaping the single-quotes around the question marks in various ways.) So--I've tried: 
my $sql = qq{SELECT * FROM $table WHERE (MATCH(Author) AGAINST(?)) ord
+er by Title};

$ding -> execute("\'+herman +melville\' IN BOOLEAN MODE") or   die   D
+BI->errstr;

[download]
But, weirdly, that gives me results on "melville" whether "herman" is in the column or not.

(I might add that I am a bookseller/coder, and not a student. The search engine on my site, www.amherstbooks.com, is not working right now because my host has asked me to tighten up my searches, which is what I'm trying to do with this.

Thanks in advance for your wisdom!

Replies are listed 'Best First'.
Re: DBI/DBD::mysql placeholders and full text searches
by ikegami (Patriarch) on Sep 29, 2008 at 00:18 UTC

    "IN BOOLEAN MODE" should definitely not be in there. Those are keywords. The replaceable parameters can *only* replace literals (and then, only some litarals). If anything works, it's 

    my $table_lit = $dbh->quote_identifier( $table );

my $stmt = qq{
   SELECT *
     FROM $table_lit
    WHERE ( MATCH( Author ) AGAINST( ? IN BOOLEAN MODE ) )
    ORDER BY Title
};

...

$sth->execute('+herman +melville');

    [download]

    If the above doesn't work, the following will definitely work: 

    my $table_lit   = $dbh->quote_identifier( $table );
my $pattern_lit = $dbh->quote( '+herman +melville' );

my $stmt = qq{
   SELECT *
     FROM $table_lit
    WHERE ( MATCH( Author ) AGAINST( $pattern_lit IN BOOLEAN MODE ) )
    ORDER BY Title
};

...

$sth->execute();

    [download]

    Update: Added backup method.

[reply]
[d/l]
[select]
      Thanks!

      You've taught me a couple of things. However, this seems to work best for my needs: 
      my $sql = qq{SELECT * FROM $table WHERE (MATCH(Author) AGAINST(? IN BO
+OLEAN MODE)) order by Title};

my $executable = '+'. $melville . ' +' . $herman;

$ding -> execute($executable) or   die   DBI->errstr;

      [download]
      Though I haven't used it, I gather that the "quote" function you suggested would offer the same sort of protection from sql injection attacks that using placeholders does.

      Question: Why the addition of the "quote_identifier" function? I experimented with it and couldn't find that it made any difference to the other code. (And as such, it sort of muddied the issue.) And it's not a user inputted variable, so it's not a safety issue as far as I know....Or am I missing something?
[reply]
[d/l]
        quote_identifier and quote convert text into SQL identifiers (e.g. table names) and SQL string literals respectively.

        By the way, 

        my $executable = '+'. $melville . ' +' . $herman;

        [download]
        is just a long winded way of saying 
        my $executable = "+$melville +$herman";

        [download]

        Something to think about: What happens if $melville contains a space?

[reply]
[d/l]
[select]

        I'm not sure how far you're going to get ... but I approach things a bit differently. I'm not disagreeing with ikegami at all here, but just your one parameter you're giving to execute. Instead of constructing a string by hardcoding everything you want to put in there, I prefer telling perl how to construct things and then using a list or array.

        For example, instead of my $executable = "+$melville +$herman"; (which is the same as what you have - the perl compiler produces the same code either way), I prefer doing the long-winded version such as: 

        my @searches = get_searches(); # e.g., qw(melville herman)
my $search_param = join ' ', map { "+$_" } @searches;
$ding->execute($search_param) or die DBI->errstr();

        [download]
        I've only really extended by another line, though if you count opcodes, mine will be much longer. The difference? Ease of change. Adding another keyword is simple - just have get_searches produce a longer list (which could be hardcoded itself, doesn't matter). You want to automatically handle multiple words by quoting them? Also simple: just change the map, e.g., map { /\s/ ? qq[+"$_"] : qq[+$_] }. It's all really straight-forward. But I deal in change more than I deal with original code, so that type of flexibility has always been important to me. Yes, it's less readable. But it's the least unreadable for the flexibility. And, after a bit more experience, it becomes not much less readable than your original version.

[reply]
[d/l]
[select]
        quote_identifier and quote convert text into SQL identifiers (e.g. table names) and SQL string literals respectively.

        By the way, 

        my $executable = '+'. $melville . ' +' . $herman;

        [download]
        is just a long winded way of saying 
        my $executable = "+$melville +$herman";

        [download]

        Something to think about: What happens if $melville contains a space?

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom