Best Practice For Passing Array Of Ids For In Statement Via Get/post

Hi,

I have a need to provide a multi level lookup for an organisation/person model. So the idea is you can search within an organisation, or go up a level to its parent and search all organisations within the parent. and again you can go up to the parents parent and search all organisations within that, and so on.

I have the code working fine so my question is not about how to do that, but how to pass the list of organisations securely to the sql statement.

I have a page where you type the persons name in an autocomplete box. I have a jquery statement that uses JSON to send a GET request to a controller function that returns the list of people based on the characters entered and the organisation selected in a drop down list. At the moment, I am passing the list of organisation ids as a hyphen separated string as GET.

This is the function that runs the query based on the GET array and returns the list of people




        public function actionGetPerson($id = null) {

            

            $res =array();


            if (isset($_GET['term'])) {


                $term = '%'.$_GET['term'].'%';

                $orgids = str_replace('-',',',$_GET['orglist']);


                $qtxt = "SELECT id as value, CONCAT(fullname,' (',orgcode,')') as label FROM vw_person WHERE ownerorgid IN ($orgids) AND statusid=:stat and fullname LIKE :fullname ORDER BY fullname";


                $command =Yii::app()->db->createCommand($qtxt);

                $command->bindValue(":fullname", $term, PDO::PARAM_STR);

                $command->bindValue(":stat", 1, PDO::PARAM_STR);


                $res =$command->queryAll();

            }

            echo CJSON::encode($res);

            Yii::app()->end();

        }



This all looks fine but its not very secure.

What is the best/most secure way to pass the list of organisations to this function, there could be upwards of a 200+ orgs. I presume if I build the list in the controller function rather than in the source page it will slow it down?

Regards

Greg J

use parameters and add each orgid as separate param (in a loop):





$params = array();

for( $x = 0; $x < count( $orgids ); $x++ ) {

  $params['<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/tongue.gif' class='bbc_emoticon' alt=':P' />' . $x] = $orgids[$x];

}


$query .= 'AND orgid IN (' . implode( ',', array_keys( $params ) ) . ')';


...


$result = $command->queryAll( $params );

...



named parameters are most secure way to pass anything to DBMS…

If the IDs are always numeric, you could use a regex to confirm that the string is acceptable before adding it to the query. In general, string concatenation in queries is (rightly) frowned upon, so if you want to avoid it completely, you could use active record and the CDbCriteria->addInCondition() method.

If you can’t use active record for some reason, you could instead build a parameterized query and bind the values to it. Something like the following would probably work:




$ids = explode('-', Yii::app()->request->getQuery('orglist', ''));

$idParams = array();


for ($i = 0; $i < count($ids); $i++)

    $idParams[":id$i"] = $ids[$i];


$qtxt = "SELECT id as value, CONCAT(fullname,' (',orgcode,')') as label FROM vw_person WHERE ownerorgid IN (".implode(',', array_keys[$idParams]).") AND statusid=:stat and fullname LIKE :fullname ORDER BY fullname";


$command =Yii::app()->db->createCommand($qtxt);

$command->bindValue(":fullname", $term, PDO::PARAM_STR);

$command->bindValue(":stat", 1, PDO::PARAM_STR);

$command->bindValues($idParams);



This will work if the IDs aren’t numeric. If they are numeric, it’s probably clearer to simply validate the string with a highly restrictive regex, such as




/^[0-9-]*$/



EDIT:

Hehe, ninja’d with an almost identical solution :P

Hi Keith,

Thank you very much for the quick response. This was exactly what I am looking for. I havent really touched on regex yet so I will leave that for a later time. It does seem it would simplify it a bit. I do like the parameter format tho so I will use this method so its consistent with the rest of my project.

Yii still amazes me with the seemingly endless ways to get things done. im usually challenging the language/framework to do what I need but Yii just has a way, and what really makes that work is the forums and online examples. The power of this forum is enormous and yet again (thanks to you) I have achieved exactly what I want to be able to do.

Just wish I had found Yii a few years ago…

Regards

Greg J

Thanks Redguy,

See comments below, these apply to you also. Thanks for the quick response.

Cheers

Greg J