Ad

Prevent SQL Injection For Bulk Prepared Raw Query Using Laravel

- 1 answer

This is my query:

$last = count($list)-1;

$sql = 'INSERT INTO table (col1, col2, col3) VALUES ';
for($i=0; $i<$last; $i++) {
    $sql .= '("'. $list[$i]['col1'] .'", "'. $list[$i]['col2'] .'", '. $list[$i]['col3'] .'), ';
}
$sql .= '("'. $list[$last]['col1'] .'", "'. $list[$last]['col2'] .'", '. $list[$last]['col3'] .') ';
$sql .= 'ON DUPLICATE KEY UPDATE ';
for($i=0; $i<$last; $i++) {
    $sql .= 'col3 = '. $list[$i]['col3'] .', ';
}
$sql .= 'col3 = '. $list[$last]['col3'];

DB::statement($sql);

Query without PHP:

INSERT INTO table (col1, col2, col3) VALUES
    ( $list[$i]['col1'] , $list[$i]['col2'] , $list[$i]['col3'] ),
    ...
    ( $list[$i]['col1'] , $list[$i]['col2'] , $list[$i]['col3'] )

ON DUPLICATE KEY UPDATE
    col3 = $list[$i]['col3'],
    ...
    col3 = $list[$i]['col3'];

I've looked at this and this. It's somewhat unclear to me how I should be doing it. Am I supposed to do it like this?

DB::connection()->getPdo()->quote($sql);

I've also read you can do something along these lines:

DB::escape($sql)

What is the best and easiest way to prevent SQL injection for the above query using Laravel?

Ad

Answer

I think this is it unless someone can do it with pure Eloquent. Hopefully this helps someone else trying to do something similar.

Since I am using col1 + col2 as a key I had to create a composite unique key like this:

CREATE UNIQUE INDEX col1_col2 ON table (col1, col2)

And here it is for posterity:

$last = count($list)-1;

$sql = 'INSERT INTO table (col1, col2, col3) VALUES ';
$values = [];

for($i=0; $i<$last; $i++) {
    $sql .= '( ?, ?, ? ), ';
    $values[] = $list[$i]['col1'];
    $values[] = $list[$i]['col2'];
    $values[] = $list[$i]['col3'];
}
$sql .= '( ?, ?, ? ) ';
$values[] = $list[$last]['col1'];
$values[] = $list[$last]['col2'];
$values[] = $list[$last]['col3'];

$sql .= 'ON DUPLICATE KEY UPDATE ';
$sql .= 'col3 = VALUES(col3)';

DB::statement($sql, $values);

Cheers!

Ad
source: stackoverflow.com
Ad