Member Avatar for webdude12

I have a PHP script I am trying to convert to PERL and need some help.

Here is the code in PHP

$store_list_name = $prod[10];
    $category = $prod[11];
    $sub_category = $prod[12];

    $searchfor = $store_list_name . "->" . $category . "->" . $sub_category;
    $getcat = mysql_query("SELECT * FROM categories WHERE (overstock_id LIKE '%|".addslashes($searchfor)."|%');",$link);
    while ($p_row =mysql_fetch_array ($getcat) ) {
    $cat_name = stripslashes($p_row[cat_name]);
    $cat_id = $p_row[cat_id];
    $sub_of = $p_row[sub_of];
    }

Here is what I have in PERL:

my $searchfor = $store_list_name . "->" . $category . "->" . $sub_category;
    my $search_by = $dbh->quote($searchfor);
    my $query = "SELECT * FROM categories WHERE overstock_id %|".$searchfor."|%";
    my $dth = $dbh->prepare($query);  
    $dth->execute();
    my $ref;
    while ($ref = $dth->fetchrow_hashref())
    {
    my $master_cat = $ref->{cat_id};
    my $sub_cat = $ref->{sub_of};
    }

The problem I am running into is, I get this error:
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '|Tools->Professional Grade Tools->Tool Sets|%' at line 1 at ./test.pl line 57, <CSV> line 585.

My first % is going bye bye.... Can some one help?

  • 3 Contributors
  • 2 Replies
  • 131 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by chrisranjana

Dani AI

Generated

The error in the trace comes from how the SQL string was built in Perl. correctly flagged the malformed query string, and pointed to DBI examples. A clearer, safer pattern is to build the LIKE pattern as a Perl scalar and pass it as a bound parameter to a prepared statement instead of trying to compose percent signs and delimiters inside the SQL literal.

$dbh->{RaiseError} = 1;    # let DBI throw on errors

my $pattern = '%|' . $searchfor . '|%';

my $sth = $dbh->prepare('SELECT cat_id, sub_of FROM categories WHERE overstock_id LIKE ?');
$sth->execute($pattern);

while (my $rec = $sth->fetchrow_hashref) {
    my $cid    = $rec->{cat_id};
    my $parent = $rec->{sub_of};
    # process $cid and $parent
}

$sth->finish;

Notes: placeholders remove manual quoting and reduce SQL-injection risk; setting $dbh->{RaiseError} makes errors easier to catch without sprinkling or die everywhere. For debugging, DBI tracing (DBI->trace) or printing the built $pattern will reveal what is actually sent to the driver. Performance caveat: a leading wildcard (%foo) prevents use of a standard index on that column; if lookups are frequent, consider normalizing the token data or using a full-text/indexed approach.

DBI attribute details and DBD::mysql specifics are documented in the DBI and DBD::mysql manuals: DBI docs and DBD::mysql docs.

Recommended Answers

All 2 Replies

Member Avatar for mattjmorrison

Perl uses the % to prefix names of associative arrays (hashes)...and since Perl interpolates double quoted strings you'll need to escape the % symbols....also, i believe your missing the word "LIKE" in your query....you also may want to add an "or die($dbh->errstr)" on your prepare and execute calls.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.