Re^2: DBI/DBD::mysql placeholders and full text searches

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?

Comment on Re^2: DBI/DBD::mysql placeholders and full text searches Download Code

Replies are listed 'Best First'.
Re^3: DBI/DBD::mysql placeholders and full text searches by ikegami (Patriarch) on Sep 29, 2008 at 02:04 UTC
`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]
Re^3: DBI/DBD::mysql placeholders and full text searches by Tanktalus (Canon) on Sep 29, 2008 at 04:05 UTC
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]
Re^4: DBI/DBD::mysql placeholders and full text searches by Gnat53 (Novice) on Sep 29, 2008 at 11:46 UTC
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]
Re^5: DBI/DBD::mysql placeholders and full text searches by ikegami (Patriarch) on Sep 29, 2008 at 12:21 UTC
It doesn't seem to have any effect on the question I was asking. True, you didn't ask to fix that bug. I experimented with it and couldn't find that it made any difference to the other code. Why do you think injecting raw text is acceptable in one spot and not the other?	[reply]
Re^3: DBI/DBD::mysql placeholders and full text searches by ikegami (Patriarch) on Sep 29, 2008 at 02:06 UTC
`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]