Re^3: Search 2 columns

You are interpolating into a string and then using that string as a sprintf template. You are also not using placeholders, nor quoting things that go directly into the SQL.

Consider the following things that can go wrong when generating your SQL:

The first character of $search is right next to a "%" character. So by putting in a character that sprintf recognizes, you can "steal" the number meant for the LIMIT clause.

my $search = "foobar";
printf "WHERE blah LIKE '%$search%' LIMIT %d,%d\n", 1, 2;

# ... LIKE '%foobar' ...
#           ^^

# WHERE blah LIKE '1.000000oobar%' LIMIT 2,0
[download]

Steal both numbers meant for the LIMIT clause, since "%" symbols in $search are not escaped

my $search = "f %d";
printf "WHERE blah LIKE '%$search%' LIMIT %d,%d\n", 1, 2;

# WHERE blah LIKE '1.000000 2%' LIMIT 0,0
[download]

Other special symbols are not escaped, and directly included in the SQL:

my $search = "' OR '1' LIKE '1";
printf "WHERE blah LIKE '%$search%' LIMIT %d,%d\n", 1, 2;

# WHERE blah LIKE '%' OR '1' LIKE '1%' LIMIT 1,2
[download]

You can even do nastier stuff with a sprintf injection attack -- like change the value of variables in the script! So it is not just the SQL statement or database that might be affected.

Solutions/suggestions:

use DBI placeholders for inserting user-supplied data into the SQL statement (for automatic escaping of SQL special chars).
Don't directly interpolate user-supplied data into a s/printf statement (to avoid sprintf injection).
Don't use s/printf when you have "%" characters all over the SQL statement (it's not clear whether the "%" chars of the SQL statement will be eaten up by s/printf or not).

blokhead

Comment on Re^3: Search 2 columns Select or Download Code

Replies are listed 'Best First'.
Re^4: Search 2 columns by roboticus (Chancellor) on Sep 22, 2007 at 16:35 UTC
Yeah ... interpolation really makes a hash of things. ;^) `%$search` ...roboticus	[reply] [d/l]
Re^5: Search 2 columns by graff (Chancellor) on Sep 23, 2007 at 15:15 UTC
Well, I did try the following test before putting "%$search%" into the snippet suggestion in my own reply: `perl -Mstrict -le 'my $s="blah"; print "%$s%"'` [download] which prints "%blah%". Putting a "%" in front of a scalar variable in a double-quoted string will not turn that variable into a hash ref. If the variable happens to already be a hash ref, then of course it will be interpolated as such, though not in a way that most folks would consider useful: `perl -Mstrict -le 'my $s={foo=>"bar"}; print "%$s%"'` [download] prints something like "%HASH(0x1801380)%". It's only arrays (and array refs) that get interpolated into the list of values when placed inside double-quotes -- and only if the sigils are right: `perl -Mstrict -le 'my %s=(foo => "bar"); print "%s"' %s perl -Mstrict -le 'my $s=[qw/foo bar/]; print "%$s%"' %ARRAY(0x1801380)% perl -Mstrict -le 'my $s=[qw/foo bar/]; print "@$s@"' foo bar@` [download]	[reply] [d/l] [select]
Re^6: Search 2 columns by roboticus (Chancellor) on Sep 23, 2007 at 15:50 UTC
Ah, well, that's what I get when I comment without testing.... ;^( I knew I got smashed before with a %, I just thought it was like $ and always messed it up. Thanks for the clarification. Now I can use % in my messages again! ...roboticus	[reply]


There's more than one way to do things
	PerlMonks