If it were on a public site I would set it so the form would have to live on the site to be able to submit, and in that case I might have been paranoid enough to work out the details of quoting the strings as well.
From what you said I can imagine that a really determined hacker might be able to nest an insert, delete, or update statement into one of the queries, and I'd like to understand for future reference how to mitigate this risk... Are you suggesting something like the following?
> $sql = "SELECT > ID,OWNER,BASEVERSION,PLATFORM,DATABASE,DBCHARSET,MIGAPPSERVER > FROM MASTERTABLE > WHERE ACTIVE LIKE $dbh->quote($qActive) > AND CUSTOMER IN ($dbh->quote($customerValueString))
Dave A.
On Thu, 25 Sep 2003 12:02:38 +0200, Peter J. Holzer wrote:
>> sub buildValueStrings {
>> my ($a) = shift;
>> my ($h) = shift;
>> for (@test = @$a) {
>> (($_ eq "0") ? (@$a = keys %$h) : "");
>> }
>> grep($_ = "'$_'", @$a);
This still allows somebody to inject sql code by embedding single quotes in the values. Use $dbh->quote() to avoid this.
hp
-- _ | Peter J. Holzer | Unser Universum w�re betr�blich |_|_) | Sysadmin WSR / LUGA | unbedeutend, h�tte es nicht jeder | | | [EMAIL PROTECTED] | Generation neue Probleme bereit. __/ | http://www.hjp.at/ | -- Seneca, naturales quaestiones
Dave Anderson wrote:
I finally solved this by doing sort of an end run around the issue. There seems to be no way to bind an arbitrary number of variables to an "IN" clause, especially when there are multiple "IN" clauses with different numbers of variables to bind for each. For example, in the SQL below, $platformValueString may have from 1 to 6 variables to bind, and $databaseValueString may have from 1 to 4 variables to bind, and there is no knowing how many there will be in any ValueString until runtime.
DISCLAIMER: This works for Oracle.... anything else and you might have to change the grep statement below or maybe more. I would rather use bind variables('?') for portability, but they simply don't work for this.
I'm getting my values for my query with the following field generated by CGI.pm:
<select name="qPlatform" size="6" multiple> <option selected value="0">All</option> <option value="1NT">WinNT</option> <option value="22K">Win2K</option> <option value="3Sun">Solaris</option> <option value="4HP">HP-UX</option> <option value="5IBM">AIX</option> </select>
This is constructed along with several other multi-select boxes using a simple subroutine that uses a lookup table similar to the following for each multi-select box:
SQL> select * from PLATFORMTAB;
ID NAME ----- ---------- 0 Choose 1NT WinNT 22K Win2K 3Sun Solaris 4HP HP-UX 5IBM AIX
Since I load this into a hash later, I have chosen to force the sort order by prepending numbers to my keys. This approach makes it easy to update the form and all related queries with a simple manual sql statement updating or inserting rows into the lookup table; I don't want to have to edit the CGI when I make such a change. In other cases where I may need to update often I use numbers like 10, 20, 30, and so on in the lookup table. I load this lookup table into a hash, both for the purpose of creating the form element and for the purpose of constructing my query:
%Platform = @{$dbh->selectcol_arrayref("SELECT ID,NAME FROM PLATFORMTAB", {Columns=>[1,2]})};
My problem was that the form would return several arrays of arbitrary size, one for each multi-select box. I won't get into the form part here; ask for it if you want it... but the following line is where I read in the array from the form, making sure the array contains a '0' if nothing else:
@qPlatform = ($q->param("qPlatform") ? $q->param("qPlatform") : "0");
I then call the subroutine that constructs a query string, using references to the above hash and array:
$platformValueString = buildValueStrings([EMAIL PROTECTED], \%Platform);
So then my subroutine looks like this:
sub buildValueStrings { my ($a) = shift; my ($h) = shift; for (@test = @$a) { (($_ eq "0") ? (@$a = keys %$h) : ""); } grep($_ = "'$_'", @$a); return join(",", @$a); }
Then, after doing this for all of my query strings, I construct my SQL statement(I've added linefeeds in this email for readability; in my CGI it's one line):
$sql = "SELECT ID,OWNER,BASEVERSION,PLATFORM,DATABASE,DBCHARSET,MIGAPPSERVER
FROM MASTERTABLE
WHERE ACTIVE LIKE $qActive
AND CUSTOMER IN ($customerValueString)
AND MILESTONE IN ($milestoneValueString)
AND OWNER IN ($ownerValueString)
AND PLATFORM IN ($platformValueString)
AND DATABASE IN ($databaseValueString)
AND DBCHARSET IN ($charsetValueString)
AND MIGAPPSERVER IN ($appServerValueString)
AND ERPCSV IN ($csvValueString)
AND ERPORACLE IN ($oracleValueString)
AND ERPPSOFT IN ($peoplesoftValueString)
AND ERPSAP IN ($sapValueString)
ORDER by ID";
After that, it's a simple matter of doing the following:
$sthMaster = $dbh->prepare_cached($sql); $sthMaster->execute();
Maybe this will help someone, maybe not.... I have had some good help from this list though, and I wanted to give back. :)
Enjoy!
Dave A.
Peter J. Holzer wrote:
On 2003-09-23 20:30:10 -0700, Dave Anderson wrote:
I am having a heck of a time doing a "SELECT FROM TAB WHERE COL1='val' and COL2 IN ('1','2')" where the '1','2' is coming from an array that is returned from a multi-select box built with CGI $q->scrolling_list with "multiple" set to "true". I see that bind_param_array can take an array as a bind value, but I want to have several arrays of varying sizes in my query. bind_param_array will not do this, nor will it work for a SELECT.
bind_param_array is for use with execute_array. It will execute the same query for each value in the array.
$sthHistory = $dbh->prepare_cached('SELECT STATUS,COMMENTS,LABEL FROM STATUSCURRENTTAB WHERE ID in ?');
You cannot expand a single "?" to a list of values. You have to construct a query string with the appropriate number of placeholders first, then you can simply call execute:
$query = 'SELECT STATUS,COMMENTS,LABEL FROM STATUSCURRENTTAB WHERE ID in (' . join(',', map { '?' } @array) . ')'; $sthHistory = $dbh->prepare_cached($query); $sthHistory->execute(@array);
hp
