Tsubasa Kato Tsubasa Kato - 7 months ago 21
Perl Question

Perl MySQL Fulltext Search

I'm new to this site.

I have the following code to search against three columns with fulltext index. (searchdatabase.title, searchdatabase.keywords, searchdatabase.description)

$SQL_QUERY=<<__CURSOR_1__;
select distinct url, keywords, description, title from searchdatabase
where match ( searchdatabase.title, searchdatabase.keywords,
searchdatabase.description ) against ('$datasent' IN BOOLEAN MODE)
order by authority_rank desc, rank desc limit 10 offset $page;
__CURSOR_1__


$dbh = DBI -> connect ($dns, $username, $password)
or &Error("Can not connect to database.");

$cursor = $dbh->prepare( "$SQL_QUERY" );
$cursor->execute;


With my limited knowledge of Perl fulltext search and SQL, I have the following code before the above code to do some SQL injection prevention and make spaces "+" in order to search.

$datasent =~ s/ / +/g;
$datasent =~ s/ / +/g;
$datasent =~ s/\n//g;
$datasent =~ s/<//g;
$datasent =~ s/\x00//g;
$datasent =~ s/\r//g;
$datasent =~ s/\x1a//g;
$datasent =~ s/\;//g;
$datasent =~ s/\*//g;
$datasent =~ s/\'//g;
$datasent =~ s/\"//g;


Is this the right way to do this?
I have a feeling something's wrong with making spaces into "+"...

Answer

DBI's placeholders are the way to go here. It will take care of escaping stuff for you.

my $sql = <<'CURSOR_1';
select distinct url, keywords, description, title
from searchdatabase where
match ( searchdatabase.title, searchdatabase.keywords, searchdatabase.description )
against (? IN BOOLEAN MODE)
order by authority_rank desc, rank desc limit 10 offset $page;
CURSOR_1

You basically put ? into the query. When you call it, pass the parameters into execute.

my $sth = $dbh->prepare( $sql );
$cursor->execute( $datasent) ;

For $page this will not work because it's not part of the WHERE clause. Instead, you should just make sure that it contains a number in case it comes from outside.

die 'offset needs to be numeric' if $page =~ /\D/;

Since you're a new user, here are a few notes about your code:

  • always use strict and use warnings – they make your life easier
  • then declare your variables with my
  • $ALL_CAPS variables are usually used for globals variables in Perl
  • in a DBI context, statement handles are usually named $sth
  • don't use the ampersand in &foo(), it doesn't do what you think
  • you don't need to put single variables into double quotes for interpolation