byterock

So I though it would be just a regular day until with me just doing a little coding, drinking my coffee* and generally enjoying life, But I was wrong.

A Little Vulnerable

So I have been babysitting and ever so slowly migrating a 15+ year old application over to a more manageable and of course we have to keep doing improvements to the code so on a very old part I found something like this. (SQL changed to protect the innocent)

my $usr_ids = join(",",@user_sel);

my $sth = $h->prepare("Select * from a_table where id in ($usr_ids)");

$sth->execute();

Params? We an't got no params. We don't need no stinking params!!

Well the old bug-bear of little Bobby Tables or SQL injection shows it ugly little head again.

But what to do? There is no bind_array, well lets just give 'execute_array' a try

my $sth = $h->prepare("Select * from a_table where id in (?)");
my $tuples = $sth->execute_array(
{ ArrayTupleStatus => \my @tuple_status },
@user_sel,
);

ora_st_execute_array(): SELECT statement not supported for array operation.

Ouch so no luck there!

Well there is a way around this when I carefully read the DBI doc, I noticed that execute

$rv = $sth->execute(@bind_values)

conveniently can take an array of param values, something I do not usually do, but this time why not take advantage of that.

So lets drop the array join, then simply create the correct number of "?" using the 'x' operator, get rid of the extra ',' at the end with chop, drop that new string into our sql and then execute with the original array. Like this;

my $params = "?," x scalar(@user_sel);
chop($test_param);
my $sth = $h->prepare("select id,name from a_test where id in ($params )");
$sth->execute(@user_sel);

And there you have it. I did add one line of code, with the 'chop' but at least I got rid of a full iteration of my list and now my code is 'Injection Safe'.

*No animals where harmed in the writing of this blog, A coffee was spilled that scared my cat and annoyed my wife but nothing serious.