variables to an sql statement 1

[gshirey]

I am trying to pass variables to a mysql statement. The variables were picked up via a CGI form. Somehow the variables just aren't working right. Observe the following:

$searchFor = em(param('searchTerm'));
$searchField = em(param('field'));
my $query;
my $query="select * from address where $searchField like \"%$searchFor%\"";

my $sth=$dbh->prepare($query); # prepare the database for the SQL query

When doing the following, I get no response from the DB. However, if instead of:
$searchfor = em(param('searchTerm'));
I placed:
$test = name;
in the sql statement it worked fine. To summarize, if I set a variable within the code, it works, if I rely on the cgi variable it fails. Any hints as to what I might be doing wrong? Is there some sort of clean function to make the variable useable?

Any help will be greatly appreciated.

 

[tsdragon]

You major problem is using em() around the values from the form. That will make the values look like &quot;<em>value</em>&quot; or something similar.

Here's another tip: instead of trying to quote values in your sql statement yourself, let the DBI do it for you. Use &quot;placeholders&quot; in the statement in place of the values, and put the variables in the execute. Like this:

$searchFor = param('searchTerm');
$searchFor = "%".$searchFor."%";
$searchField = param('field');
my $query="select * from address where $searchField like ?";
my $sth=$dbh->prepare($query); # prepare the database for the SQL query
$sth->execute($searchFor);

Tracy Dryden
tracy@bydisn.com

http://www.bydisn.com

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[MikeLacey]

Have you tried:

my $query=&quot;select * from address where $searchField like '%$searchFor%'&quot;;

(single quotes in where clause, instead of &quot


Mike
michael.j.lacey@ntlworld.com
Email welcome if you're in a hurry or something -- but post in tek-tips as well please, and I will post my reply here as well.

 

[gshirey]

Thanks for the help. Indeed it was the <em> around the variable contents that was hurting me. I appreciate giving a question at the end of the day and having good answers in the morning.