I have followed your guidance and created a working solution, as seen below. Can you confirm that I have done this in the safest / most appropriate way? It is working but just need to know that I am not opening myself up to a sql injection.
I am importing the data from an API on an external website (survey monkey). So, yes, the data is coming from multiple end users and I am downloading the batches for insertion into my database.
The problem is that if I use PDO binding (which I really want to) then I cannot use "ON DUPLICATE" and I really need a mechanism to handle duplicates.
The alternative is to query the database, find any duplicates, delete them and then do a batch insert.
The other alternative is to extend the batch insert function on Yii so that I can include the ON DUPLICATE statement but I don’t know how to do that. I have raised the question today on the forum but no answers yet.
<?php
namespace app\models;
use Yii;
class Toolkit_BulkInsert extends \yii
{
public $tableName;
public $data=[];
private $values_to_bind=[];
private $colNames;
public function buildSQL(){
// get list of columns
// IMPORTANT: requires that all arrays have the same columns in the same order!
$this->colNames="(".implode(",",array_keys($this->data[0])).") ";
// now build the sql in preparation for binding
$x=0;
$sql_values=[]; // eg insert into x values (:a,:<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='B)' />,(:c,<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/biggrin.gif' class='bbc_emoticon' alt=':D' />)...
$this->values_to_bind=[]; // eg [:x=>abc]
foreach ($this->data as $arr) {
$ADO_id = [];
$x++;
foreach ($arr as $key=>$value){
$ADO_id[]= " :".$key."_".$x.""; // :a_1, :b_1
$this->values_to_bind = $this->values_to_bind + [":".$key."_".$x=>$value];
}
$sql_values[] = "(".implode(",",$ADO_id).")";
}
// create the update instruction
// eg a=VALUES(a), b=VALUES(<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='B)' />
$sql_update=[];
foreach($this->data[0] as $key=>$value){
$sql_update[] = $key." = VALUES (".$key.") ";
}
$this->sql =
"INSERT INTO ".$this->tableName." ".
$this->colNames.
"VALUES ". implode(",",$sql_values) ." ".
"ON DUPLICATE KEY UPDATE ".implode(",",$sql_update);
// echo $this->sql."<br>";
// Toolkit::printObject($this->values_to_bind,0);
}
public function connect(){
$this->connection = Yii::$app->getDb();
return $this->connection;
}
public function execute(){
$command = $this->connection->createCommand($this->sql);
$command->bindValues($this->values_to_bind);
return $command->execute(); // will return the count of records inserted
}
}