in reply to DBI:MYSQL placeholders and full text searches

"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.

Replies are listed 'Best First'.
Re^2: DBI/DBD::mysql placeholders and full text searches
by Gnat53 (Novice) on Sep 29, 2008 at 01:50 UTC
    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]
        Tanktalus:

        I'm with you, actually. I was just abbreviating and trying to show the protocol for solving my problem. My $executable is actually constructed with a loop from an array.

        Thanks for your time!

        ikegami:

        That still doesn't explain why you added quote_identifier. It doesn't seem to have any effect on the question I was asking. Does it, for example, make a one-off query more efficient?

        And thank you for your time!
[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]

In Section Seekers of Perl Wisdom