Larry W. Virden wrote:
> I've a case where a function is called with a string provided by a
> user, and some legacy code then puts that string into a select
> statement for dbi. The code currently reads:
>
> my ($query) = $dbconn->prepare(
> "Select * from my_table where last_name LIKE
> '$SearchName'");
> $query->execute() or return \...@retval; #problem return empty array
>
> Now, one of the issues that comes up is the situation where the user's
> string doesn't match the case of the name in the table.
>
> For instance, if they pass a "McDonnel" as the string, but in the
> table, it is "Mcdonnel", of course there is no match.
>
> So, I was thinking of modifying this to read
>
> my ($query) = $dbconn->prepare(
> "Select * from my_table where UPPER(last_name) LIKE
> UPPER('$SearchName')");
>
> Are there any gotchas in going this route? Is there a better way of
> doing this?
>
>
I'd really avoid concatenating user supplied strings into your SQL. Use
parameters instead:
select * from my_table where last_name like ?
then use bind_param or exeute(my_parameter_list).
If you don't know why search for SQL Injection or see Taint mode in Perl
and DBI (TaintIn).
You don't say what database this is. It is not always UPPER especially
when the strings might be unicode (Oracle uses nls_upper). Other than
that upper(column) = upper(?) is ok.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
