Hard coded vs. variable assignment using in SQL statement.

[Jack]

Hi,

I use MySQL 5.0 with perl (the latest version) in FreeBSD 5.3.
I found that if I assign a hard coded string (with double quote) to the
string variable which in turns become part of the SQL statement, the SQL
does return result.
If I assign a value which is returned from an array in perl (eg.
@array[1]) and use this variable in the SQL statement, the SQL does not
return any result.

For example:
The following code does not return any result when I use it in SQL
statement:
my $mycustcode = "@outlets[1]"; # this array contains string "07-2-0057"

However, if I use the following hard coded assignment, the SQL statement
returns result:
# my $mycustcode = "07-2-0057";

Here is the SQL statement for MySQL 5.0:
$create_view_sql = qq {create view $viewtab as
select c.custcode, c.custname, c.type, sum(t.netsales)
as sales
from customer c, transaction t
where c.custcode = t.custcode
and date(t.date) >= "$start_date"
and date(t.date) <= "$end_date"
and (c.type = "EXPORT" or c.type = "LOCAL")
and (c.custcode = "$mycustcode")
group by c.custcode;};

I may be using incorrect way of spliting the tokens, here is the
function to extract a list of the token:
$outlet_str = $in{'outlets'}

Thanks
Jack

 

[Ken]

Hi,

I use MySQL 5.0 with perl (the latest version) in FreeBSD 5.3.
I found that if I assign a hard coded string (with double quote) to the
string variable which in turns become part of the SQL statement, the SQL
does return result.
If I assign a value which is returned from an array in perl (eg.
@array[1]) and use this variable in the SQL statement, the SQL does not
return any result.

For example:
The following code does not return any result when I use it in SQL
statement:
my $mycustcode = "@outlets[1]"; # this array contains string "07-2-0057"

Are you sure that @outlets[1] is the correct way to access the variable
you want? If it is an array then it should be $outlets[1].

- Ken

 

[sam]

Hi,

I use MySQL 5.0 with perl (the latest version) in FreeBSD 5.3.
I found that if I assign a hard coded string (with double quote) to
the string variable which in turns become part of the SQL statement,
the SQL does return result.
If I assign a value which is returned from an array in perl (eg.
@array[1]) and use this variable in the SQL statement, the SQL does
not return any result.

For example:
The following code does not return any result when I use it in SQL
statement:
my $mycustcode = "@outlets[1]"; # this array contains string "07-2-0057"

Are you sure that @outlets[1] is the correct way to access the variable
you want? If it is an array then it should be $outlets[1].

OK, I changed it to "$outlets[1]", but the query still does not return
result. I have also checked its value with the print $outlets[1]
statement, it prints out the desire value as I expected. But when it
used in SQL statement, it failed to match with the AND statement.

Another strange issue is if I assigned "$outlets[0]" to the variable,
the SQL statement does return result. There 3 elements in the error,
$outlets[1] is the second element.

Thanks
Sam

Thanks
Sam

 

[Jack]

Hi,

I use MySQL 5.0 with perl (the latest version) in FreeBSD 5.3.
I found that if I assign a hard coded string (with double quote) to
the string variable which in turns become part of the SQL statement,
the SQL does return result.
If I assign a value which is returned from an array in perl (eg.
@array[1]) and use this variable in the SQL statement, the SQL does
not return any result.

For example:
The following code does not return any result when I use it in SQL
statement:
my $mycustcode = "@outlets[1]"; # this array contains string "07-2-0057"

Are you sure that @outlets[1] is the correct way to access the
variable you want? If it is an array then it should be $outlets[1].

OK, I changed it to "$outlets[1]", but the query still does not return
result. I have also checked its value with the print $outlets[1]
statement, it prints out the desire value as I expected. But when it
used in SQL statement, it failed to match with the AND statement.

Another strange issue is if I assigned "$outlets[0]" to the variable,
the SQL statement does return result. There 3 elements in the error,
$outlets[1] is the second element.

The value of the custcode is 07-3-0037, when it is used in MySQL
statement, is there a way to force it to be a string rather than a date
format?
In the SQL statement I wrote (in perl), regardless of having double
quote or not, there is not result return from the following statement:
AND c.custcode = "$mycustcode"

Thanks

 

[Tad McClellan]

my $mycustcode = "@outlets[1]"; # this array contains string "07-2-0057"

You should always enable warnings when developing Perl code.

 

[Tad McClellan]

OK, I changed it

But Ken made that suggestion to Jack and you are sam.

Do you (sam) have access to Jack's code somehow?

 

[Jack]

sam wrote:


The value of the custcode is 07-3-0037, when it is used in MySQL
statement, is there a way to force it to be a string rather than a date
format?
In the SQL statement I wrote (in perl), regardless of having double
quote or not, there is not result return from the following statement:
AND c.custcode = "$mycustcode"

Perl does not have a primitive, built-in date scalar value. It has only
numeric, string, and reference scalar values, so '07-3-0037' IS a
string.

You need to post a complete, working, short-as-possible program that
anyone can run to get more help. In general, you should print out the
values of variables to see what they contain, but you seem to be doing
that.
[/QUOTE]
Here is a more complete version of the perl code.
The following perl code does return result when execute the SQL statement:
@outlets = ("07-6-0057","07-3-0051","07-2-0036");
my $mycustcode = "$outlets[1]"; # also work for [0] or [2]
$create_view_sql = qq {create view $viewtab as
select c.custcode, c.custname, c.type, sum(t.netsales) as sales
from customer c, transaction t
where c.custcode = t.custcode
and c.custcode = "$mycustcode"
group by c.custcode;};

However if I change the code to be a bit more dynamic like the following:
In html.pl: # the following value (in the OPTION tag) will be submitted
to the query1_result.cgi.

while ($aref = $sth->fetchrow_arrayref){
print "<OPTION value=$aref->[0],>$aref->[1]: $aref->[2]</Option>\n";
}

In query1_result.cgi:

sub split_outlets_to_array
{
my ($s) = @_;
@outlets = split(',',$s);
return @outlets;
}

$outlet_str = $in{'outlets'}; # the outlets contains a list of custcodes
that seperated by comma.
@outlets_array = &split_outlets_to_array($outlet_str);
my $mycustcode = "$outlets_array[0]"; # but value of [1] and [2] can't
make the following SQL statement return a result.

$sql = qq {
select c.custcode, c.custname, c.type, sum(t.netsales) as sales
from customer c, transaction t
where c.custcode = t.custcode
and c.custcode = "$mycustcode"
group by c.custcode;};

Summary:
The problem with the second case is that outlets_array[0] does make the
SQL statement return result, but [1] and [2] does not.
With the first case (with hardcoded values), all elements of
outlets_array does make the SQL return result.
There may be problem in the code $in{'outlets'}. However from printing
each element of the array on the html page, I found nothing wrong with
the value, they are all printed in the following format on the html page:
outlets[0]: 07-6-0057
outlets[1]: 07-3-0051
outlets[2]: 07-2-0036

Unless the code of $in{} did something wrong behind the scene, I don't
know what caused this error when execute it with the SQL statement.
I tried to turn on warninig with -w, but not sure how to see them when
running in web browser.

Thanks
Jack

 

[Matt Garrish]

I tried to turn on warninig with -w, but not sure how to see them when
running in web browser.

Sorry, but your post is too convoluted for my poor brain at this hour. I
will just mention that you can resolve the above by adding:

use CGI::Carp qw/fatalsToBrowser warningsToBrowser/;

Matt

 

[Jack]

Hi,

Sorry
However, if I use the following hard coded assignment, the SQL statement
returns result:
# my $mycustcode = "07-2-0057";

Here is the SQL statement for MySQL 5.0:
$create_view_sql = qq {create view $viewtab as
select c.custcode, c.custname, c.type, sum(t.netsales)
as sales
from customer c, transaction t
where c.custcode = t.custcode
and date(t.date) >= "$start_date"
and date(t.date) <= "$end_date"
and (c.type = "EXPORT" or c.type = "LOCAL")
and (c.custcode = "$mycustcode")
group by c.custcode;};

I just found that the value of $mycustcode contains a leading space,
thus sql failed to match the right record.
This also explained why the first element has no such problem.
Sorry for being so careless.

 

[sam]

I just found that the value of $mycustcode contains a leading space,
thus sql failed to match the right record.
This also explained why the first element has no such problem.
Sorry for being so careless.

I tried to remove the leading space, but the regular expression failed
to remove it. It may be not a whitespace, what what is it?
Here is the html string being passed to the $in{'outlets'} perl code:
outlets=07-6-0057%2C&outlets=07-3-0051%2C&outlets=07-2-0036
What is %2C here?

The code I used to strip the white space is:
$str =~s/[ ]//g;

Thanks
Sam

 

[Darin McBride]

I tried to remove the leading space, but the regular expression failed
to remove it. It may be not a whitespace, what what is it?
Here is the html string being passed to the $in{'outlets'} perl code:
outlets=07-6-0057%2C&outlets=07-3-0051%2C&outlets=07-2-0036
What is %2C here?

%2C == the comma character.

The code I used to strip the white space is:
$str =~s/[ ]//g;

Better:

$str =~ s/\s+//g;

You may also want to try passing the string through Data:umper to
make sure you know exactly what character it is (if it's non-printable,
Data:umper may translate it to printable for you).

Also, you really should stop putting values in your SQL. As much as
possible, that is.

$create_view_stmt = qq{create view $viewtab as
select c.custcode, c.custname, c.type, sum(t.netsales) as sales from
customer c, transaction t
where c.custcode = t.custcode
and date(t.date) >= ?
and date(t.date) <= ?
and c.type in (?,?)
and c.custcode = ?
group by c.custcode};


Then, when you go to execute this:

$res = $sth->execute($start_date,
$end_date,
qw(EXPORT LOCAL),
$mycustcode);

Then you don't need to worry about special character as much. (Stray
characters are a different problem no matter how you do things.)

I would prefer putting a placeholder in for the view name, too, but I
doubt many (if any?) database vendors support that. If any do, it's
unlikely MySQL does.

Then again, MySQL may not even support this much for a create statement
- I'm not too sure. Then I presume you're using the DBI 'quote'
function to hide special characters properly?