Including variables in a query

[techlighthouse]

I need to enable users of my membership database to query the database and identify other members meeting some criteria. The following code snippet with hard coded search criteria works well:


// $searchfield = "street_name";
// $searchterm = "Greystone Drive";
$sql = 'SELECT * '
. ' FROM `residents` '
. ' WHERE street_name LIKE "Greystone Drive" ORDER BY street_no';


All of the pertinent records are found and ordered appropriately.

Removing the comments from the code results in no items found:


$searchfield = "street_name";
$searchterm = "Greystone Drive";
$sql = 'SELECT * '
. ' FROM `residents` '
. ' WHERE $searchfield LIKE $searchterm ORDER BY street_no';


It seems as though this should work but I'm obviously doing something wrong. All suggestions are appriciated.

Thanks.

[orbic1]

. ' WHERE $searchfield LIKE $searchterm ORDER BY street_no';

Try:

. ' WHERE $searchfield LIKE "$searchterm" ORDER BY street_no';

as in your original

[Ron]

There's a vulnerability to SQL injection there!

Someone could add something similar to

$serchterm= "main \" UNION SELECT * FROM IMPORTANT_TABLE where I.foo LIKE \"(something in I.foo)"

or however you're going to give them the ability to set $serchterm.

This code won't work as wirtten, it's just pseudocode. Point is that you gotta watch out for the way you validate the user's input.


Or so I'm told.

[techlighthouse]

Originally posted by orbic1
Try:

. ' WHERE $searchfield LIKE "$searchterm" ORDER BY street_no';

as in your original

Made the change -- actually copied and pasted the above code and it still doesn't work. No records found. FWIW, I also have the code echo the sql query and it's reported as:


SELECT * FROM `residents` WHERE $searchfield LIKE "$searchterm" ORDER BY street_no


I guess I need to find something else to try.

[techlighthouse]

Originally posted by Ron
There's a vulnerability to SQL injection there!

Someone could add something similar to

$serchterm= "main \" UNION SELECT * FROM IMPORTANT_TABLE where I.foo LIKE \"(something in I.foo)"

or however you're going to give them the ability to set $serchterm.

This code won't work as wirtten, it's just pseudocode. Point is that you gotta watch out for the way you validate the user's input.

Thanks for the warning!

I'm planning to use a form with a drop-down box to select the search field, residents, in this case. No opportunity for filling in anything other than the choices I give them.

They will, of course, have to enter the search term, Greystone Drive, in the original example. I can see the problem you're describing though. You're right about validating the input. Some fields -- street_name being one of them -- have only a finite number of possibilities so they can be hard coded into another drop-down box. Others, like last names, have an almost infinite variety and will change regularly, so some validation will be necessary.

[Piyer]

I think it should be more along the lines of this:

$query = "SELECT * FROM residents WHERE ".$searchfield." LIKE ".$searchterm." ORDER BY street_no"; 

[techlighthouse]

Nope, still doesn't work. But the code does closely resemble some on one of my books on searching based on form input:


$query = "SELECT * FROM residents WHERE ".$searchtype." LIKE '%".$searchterm."%' ORDER BY street_no";


so I modified it with my variable names as below.


$sql = "SELECT * FROM residents WHERE ".$searchfield." LIKE '%".$searchterm."%' ORDER BY street_no";


Viola! It worked! And it still works if you remove the wildcards:


$sql = "SELECT * FROM residents WHERE ".$searchfield." LIKE '".$searchterm."' ORDER BY street_no";



So the key is having the single quotes around the content to be looked for. You can make this search more flexible by allowing the user to also select the sort field using the same syntax as in the earlier $searchfield expression:


$sql = "SELECT * FROM residents WHERE ".$searchfield." LIKE '%".$searchterm."%' ORDER BY ".$orderfield;



Thanks for everyone's help. I hope it helps others in the future, too.

[Spathiphyllum]

Originally posted by techlighthouse
I'm planning to use a form with a drop-down box to select the search field... No opportunity for filling in anything other than the choices I give them.

...Some fields -- street_name being one of them -- have only a finite number of possibilities so they can be hard coded into another drop-down box...

Someone savvy enough to insert Ron's "I'm goin to compromise this database" code is savvy enough to modify a post request from your HTML. There is really no such thing as hard-coding form data. I'm afraid you should try to check the input from all user-defined variables (even your "hard-coded" ones) just to make sure. Maybe something as simple as setting a limit on the length of the input string would do. I dunno... but it would be better than no taint check at all.

[gerilya]

Originally posted by Spathiphyllum
Someone savvy enough to insert Ron's "I'm goin to compromise this database" code is savvy enough to modify a post request from your HTML. There is really no such thing as hard-coding form data. I'm afraid you should try to check the input from all user-defined variables (even your "hard-coded" ones) just to make sure. Maybe something as simple as setting a limit on the length of the input string would do. I dunno... but it would be better than no taint check at all.

Hard-coding data is fine, actually, if you take a moment (literally) to add a timestamp and authenicate it later.
Just add 3 hidden fields to the form: a random number (1-10000), a timestamp and HMACed value of that random number, timestamp, IP and referrer combined with your private key then check that the form was submitted within given time interval and the timestamp wasn't tampered.
I know that MD5 is not bullet proof, but, hey, auth.net use it, so it surely should be enough for this application

[gerilya]

Oh, and if someone really wants to inject malicious code into SQL query, it should always contain

LOAD DATA INFILE '/etc/passwd' INTO TABLE ....

otherwise, it's not worth the trouble

[Spathiphyllum]

Originally posted by gerilya
...Just add 2 hidden fields to the form: a timestamp and HMACed value of that timestamp, IP and referrer combined with your private key...

TIMTOWTDI

Actually, while this is another way of checking the entire posted hash, if the hacker was quick enough and left the two hidden integrity fields alone, then modifying the other fixed-value variables could still sneak in rogue code. Would you not still need a taint check on the user supplied variables? And if you have a form with lots of fields, wouldn't you tick off clients that kept timing out before they entered all of their data? I've had that happen after filling in a long form. Needless to say. I was not pleased.

As far as auth.net and their validation form(?), are you aware of any backend taint checks on input variables after seeing source code or are you just looking at the frontend, html code and noticing their field names and values? Maybe it's enough for their purposes and I'm in no position to judge. But taint checking every non-script produced value is important.

[gerilya]

Originally posted by Spathiphyllum
TIMTOWTDI

Actually, while this is another way of checking the entire posted hash, if the hacker was quick enough and left the two hidden integrity fields alone, then modifying the other fixed-value variables could still sneak in rogue code. Would you not still need a taint check on the user supplied variables? And if you have a form with lots of fields, wouldn't you tick off clients that kept timing out before they entered all of their data? I've had that happen after filling in a long form. Needless to say. I was not pleased.

I first described the simplified version of Auth.net authentification then realized that it would affect security. You were quick enough to catch me before I edited the post
The 3rd parameter (random variable) covers this case and this is how it's done in Auth.net. 5mins timeout works for them.

As far as auth.net and their validation form(?), are you aware of any backend taint checks on input variables after seeing source code or are you just looking at the frontend, html code and noticing their field names and values? Maybe it's enough for their purposes and I'm in no position to judge. But taint checking every non-script produced value is important. [/B]

I am aware of their SIM (Simple Integration Method) and AIM (Advanced Integration Method) integration guidelines and some aspects of implementation of SIM interface library, which they distribute.
They only authentificate TIMESTAMP, random variable, total amount and optionally currency may be 1 or 2 more parameters which I can't recall right now.
The total number of parameters one can pass exceeds 2 dozens.

[gerilya]

BTW, TIMTOWTDI only works for PHP.
For Perl, it's your way or face-the-consequences: one *has* to strip all the funny characters from the input variables because it is so much easier to inject malicious code into Perl

[Spathiphyllum]

Originally posted by gerilya
...it is so much easier to inject malicious code into Perl

Noooow you tell me... crap!

[gerilya]

How about this: it requires a good knowledge of Perl to write a safe code?