Safe Query Builder SQL Query Construction

Yii 1.1 General Discussion
1

Does query builder sanitize the $_GET params? Is there a better, more secure way to create this query?


if(isset($_GET['loc']))

  $loc=$_GET['loc'];

if(isset($_GET['promoID']))

  $promoID=$_GET['promoID'];

	    	

// SELECT userID FROM promo WHERE promoID = $promoID and location = '$loc'

$results = Yii::app()->db->createCommand()

  ->select('userID')

  ->from('promo')

  ->where('promoID=:promoID and location=:loc', array(':promoID'=>$promoID, ':loc'=>$loc))

  ->queryAll();

Thanks

2

Any time you pass your variables indirectly like this:


  ->where('promoID=:promoID and location=:loc', array(':promoID'=>$promoID, ':loc'=>$loc))

they are sanitized.

3

@joev - what if $_GET[‘loc’] and $_GET[‘promoID’] are not defined ?

In this case, $promoID and $loc will be undefined too, so you’ll get a warning.

A better way would be:




$loc=$promoID=null;

if(isset($_GET['loc']))

  $loc=$_GET['loc'];

if(isset($_GET['promoID']))

  $promoID=$_GET['promoID'];


if($loc!==null&&$promoID!==null)

{

$results = Yii::app()->db->createCommand()

  ->select('userID')

  ->from('promo')

  ->where('promoID=:promoID and location=:loc', array(':promoID'=>$promoID, ':loc'=>$loc))

  ->queryAll();

}
4

you can use




$loc = Yii::app()->request->getParam( 'loc', 'default value when "loc" is not passed' );

or for POST params




$loc = Yii::app()->request->getPost( 'loc', 'default value when "loc" is not passed' );
5

Thanks for the info. I do have my variables set to default values just did not show that in the original post. I was more concerned about the expected $_GETs being altered leaving the site vulnerable to a SQL injection or xxs.

The real life usage will be through a QR code.

6

Thanks. I like this way. So much to learn.