Sanitizing Large Sql String

Yii 1.1 General Discussion
1

Is there a Yii way of sanitizing a large sql string built using a for/while loop?

The example below shows an sql string generated from a csv file




       $startSql = "INSERT INTO mytable (column1, column2, column3) VALUES";

			if (($handle = fopen('data.csv', "r")) !== false) {

			    while (($data = fgetcsv($handle, 1000, ",")) !== false) {

                                //build each value statement with no sanitization!

			        $insertArray[]="('$data[0]','$data[1]','$data[2]')";

			        

			    }

			    fclose($handle);

			}

			$sql=implode(",",$insertArray);

			$commitSql=$startSql.$sql;


                         Yii::app()->db->createCommand($commitSql)->execute();

I know that bindParam and bindValue cannot be used in this instance. Is there a better way to construct such a query that can make use of Yii’s paramater binding or do I need to use addslashes etc…

2

You should definitely consider binding. Prepare a query to insert a single row first, then bind each row’s data to that in turn and execute.

If there’s a lot of data to import, you might want to look into LOAD DATA INFILE, assuming you’re using MySQL.

It’s always risky trying to handle sanitization yourself. I’d recommend against it if at all possible.

3

Thanks Keith. This method combined with upping the max_allowed_packet limit works well, is very fast and allows alot of freedom with the db fields (the above example is very simplified). I was just wondering if there are other sanitization methods offered by Yii besides the PDO param binding.