0

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?

Improve this question
16
  • Use prepared/parameterized queries laravel.com/docs/5.2/database#running-queries Commented Jan 6, 2016 at 13:16
  • Can you show me an example of how I would do it for the above query? Also I'm using Laravel 4.2 if that matters. Commented Jan 6, 2016 at 13:18
  • 2
    It should be pretty self explanatory, you can find the DB::insert for 4.2 here laravel.com/docs/4.2/database#running-queries Only change you need to do is to change array(1, 'Dayle') to a variable, initialize it as an array before your query and push the values you want added into it (in the correct order). Commented Jan 6, 2016 at 13:25
  • I've already looked at that. It's unclear to me how I would write a large prepared query that way though. Commented Jan 6, 2016 at 13:28
  • Just have it the exact same as you have it now, except you change all the variables in the query string into placeholders ? and instead push the variables into an array that you later send into the DB::insert method. Commented Jan 6, 2016 at 13:29

3 Answers 3

Reset to default
3

The best and easiest way to prevent SQL injection is to use parameterised queries. How to do it for bulk inserts was answered many times already, e.g. here: Insert multiple rows with PDO prepared statements

EDIT:

To make it crystal clear, to execute parametrised query in Laravel you do 

DB::insert($query, $params);

where in pure PDO answers it is 

$stmt = DB::getInstance()->prepare($query);
$stmt->execute($params);

The question how to prepare the $query and $params for bulk inserts has been answered already, massively discussed for pros and cons, and some alternative solutions for edge case scenarios emerged. Please read them and understand how it works.

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

That link is helpful but I'm asking if there's a right way to do it with Laravel.
Since you are asking about raw queries, framework doesn't really matter, as you make no use of its model layer.
I don't think the above query can be written in Laravel Eloquent. I tried but it becomes a for loop which hits the database n number of times based on how many items are in $cardQueryList so I changed it to a prepared raw so it only hits the database once.
That's a very good optimisation step, and I gave you 2 links to answer the question how to do it safely.
Optimally I'd like to do it in the way that @JimL mentioned. It's unclear to me how though I would do it for a bulk statement like this. I'd like to keep things in Laravel as much as possible.
|
0

looks like an issue when using raw queries. Found a great article and wanted to pass it along for anyone else that may need it.http://fideloper.com/laravel-raw-queries

Laravel does use prepared statements as barry mentioned, but it looks like you need to specifically pass variables in an array of bindings for this to happen with raw queries.

 $someVariable = Input::get("some_variable");

$results = DB::select( DB::raw("SELECT * FROM some_table WHERE some_col = :somevariable"), array(
   'somevariable' => $someVariable,
 ));
Improve this answer

Comments

0

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!

Improve this answer

8 Comments

Yes, you get it right. Few things to consider tho: row1 kind of names may be a bit confusing for someone else who you are trying to help. It really should be filed1 or column1. Please ensure your ON DUPLICATE stuff does exactly what you expect, otherwise you will make more harm than good to ones who trying to do something similar.
I updated to col1, col2, etc. I'm trying to use col1 + col2 as the key. I'll go test some more.
@AlexBlex They don't care, they just want spoon feeding. "Here is my problem - please write the code for me so it works" RTFM is not something they can handle
@ash You're aware that I answered my own question and the answer I posted here is mine? What is your problem? If you don't want to help don't post.
@rotaercz i was commenting to Alex Blex. I am willing to help, guide and metor through showing you and others how to find the answers for yourself. So you will improve. I absolutely do not spoon feed, that teaches nothing.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.