How to use safe raw queries against sql injections in laravel4 for INSERT IGNORE

Question

How to use safe raw queries against sql injections in laravel4 for INSERT IGNORE

1

My laravel 4 query is like below:

foreach (Input::get('classrooms') as $keyc=>$valuec) {
        foreach (Input::get('subject') as $keys=>$values) {
        $valuesArray[] = "('".$valuec."','".$values."')"; 
            }
        }
        $someVariable = implode(",",$valuesArray);
        DB::select( DB::raw("INSERT IGNORE INTO classrooms_subjects (`classroom_id`,`subject_id`) VALUES $someVariable"));

I am really concerned if thats the safest way and any solution for this..

I have done some research and found a way but not sure if its safe: please let me know if below code is safe:

$sql = "INSERT INTO classrooms_subjects (".implode(",", $columns).") ";
        $sql .= " SELECT ".implode(",", $columns)." FROM classrooms_subjects WHERE id IN (".$toCopy.")";
        DB::insert($sql);

asked May 31 '16 at 7:43

user2677125

6711

Possible duplicate of How can I prevent SQL-injection in PHP? – Epodax May 31 '16 at 7:50

Just to ask, why do you use raw SQL instead of the eloquent model? – TheFallen May 31 '16 at 8:33

just dont know how to do INSERT IGNORE through eloquent – user2677125 May 31 '16 at 9:08

add a comment

1 Answer 正确答案

active oldest votes

来自 https://stackoverflow.com/questions/37539763/how-to-use-safe-raw-queries-against-sql-injections-in-l...

Raw Queries in Laravel

4 YEARS AGO
laravel

Business logic is often complicated. Because of this, we often need to write our own SQL queries. Luckily, Laravel's query builder has the tools we need to safely run such queries.

A key concern when writing our own queries is protecting our application from SQL injection attacks. Normally, the query builder does this for us. However, when we write our own SQL, we need to make sure we don't inadvertently remove this protection.

Here's what we want to avoid:

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

$results = DB::select( DB::raw("SELECT * FROM some_table WHERE some_col = '$someVariable'") );

In the above query, we're directly adding user input into the query without sanitizing it. This leaves us open to attack!

DB::raw() is used to make arbitrary SQL commands which aren't parsed any further by the query builder. They therefore can create a vector for attack via SQL injection.

Since the query builder is using PDO in the background, we know there is a way to bind parameters to our query so it will sanitize the bound variables.

Now, as you've seen, arbitrary (raw) queries are done in the query builder using the DB::select() method. Let's look at the select() method in Illuminate\Database\Connection to see if it has any way to bind our parameters:

public function select($query, $bindings = array())
{
    return $this->run($query, $bindings, function($me, $query, $bindings)
    {
        if ($me->pretending()) return array();

        // For select statements, we'll simply execute the query and return an array
        // of the database result set. Each element in the array will be a single
        // row from the database table, and will either be an array or objects.
        $statement = $me->getPdo()->prepare($query);

        $statement->execute($me->prepareBindings($bindings));

        return $statement->fetchAll($me->getFetchMode());
    });
}

Perfect! We see above that we can pass an array of bindings to the select() method. This array is bound to the query via the PDO connection.

We can, therefore, change our previous query in a way that sanitizes the user input:

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

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

Voìla! Safe queries!

Lastly, if you are performing queries which don't return data, then using a SELECT query will result in errors. For example, if you want to start the auto-increment ID of a MySQL table to something other than zero, we can use the statement method. With statement, we don't need to use the raw() method:

// Warning: This is a MySQL-specific query
DB::statement( 'ALTER TABLE HS_Request AUTO_INCREMENT=9999' );

The statement method can also accept parameters:

DB::statement( 'ALTER TABLE HS_Request AUTO_INCREMENT=:incrementStart', array('incrementStart' => 9999) );

4 YEARS AGO
laravel

Business logic is often complicated. Because of this, we often need to write our own SQL queries. Luckily, Laravel's query builder has the tools we need to safely run such queries.

A key concern when writing our own queries is protecting our application from SQL injection attacks. Normally, the query builder does this for us. However, when we write our own SQL, we need to make sure we don't inadvertently remove this protection.

Here's what we want to avoid:

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

$results = DB::select( DB::raw("SELECT * FROM some_table WHERE some_col = '$someVariable'") );

In the above query, we're directly adding user input into the query without sanitizing it. This leaves us open to attack!

DB::raw() is used to make arbitrary SQL commands which aren't parsed any further by the query builder. They therefore can create a vector for attack via SQL injection.

Since the query builder is using PDO in the background, we know there is a way to bind parameters to our query so it will sanitize the bound variables.

Now, as you've seen, arbitrary (raw) queries are done in the query builder using the DB::select() method. Let's look at the select() method in Illuminate\Database\Connection to see if it has any way to bind our parameters:

public function select($query, $bindings = array())
{
    return $this->run($query, $bindings, function($me, $query, $bindings)
    {
        if ($me->pretending()) return array();

        // For select statements, we'll simply execute the query and return an array
        // of the database result set. Each element in the array will be a single
        // row from the database table, and will either be an array or objects.
        $statement = $me->getPdo()->prepare($query);

        $statement->execute($me->prepareBindings($bindings));

        return $statement->fetchAll($me->getFetchMode());
    });
}

Perfect! We see above that we can pass an array of bindings to the select() method. This array is bound to the query via the PDO connection.

We can, therefore, change our previous query in a way that sanitizes the user input:

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

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

Voìla! Safe queries!

Lastly, if you are performing queries which don't return data, then using a SELECT query will result in errors. For example, if you want to start the auto-increment ID of a MySQL table to something other than zero, we can use the statement method. With statement, we don't need to use the raw() method:

// Warning: This is a MySQL-specific query
DB::statement( 'ALTER TABLE HS_Request AUTO_INCREMENT=9999' );

The statement method can also accept parameters:

DB::statement( 'ALTER TABLE HS_Request AUTO_INCREMENT=:incrementStart', array('incrementStart' => 9999) );

来自 http://fideloper.com/laravel-raw-queries

如何去逃避参数传递到4个原laravel查询？我希望像DB::escape()（这听起来耳熟，也尝试从laravel，3）DB：：（报价）（我认为可以通过可PDO对象）

$query = DB::select("SELECT * FROM users WHERE users.id = " . DB::escape($userId));

我们不能用选择法与占位符作为上述只是一个简单的例子，我们要实现的目标。我们有几个嵌套的SELECT查询，无法适应查询生成器有一个大的自定义查询。

逃避的东西插在laravel 4之前最好的方法是什么？

编辑

我刚发现你可以访问和使用PDO对象的引用功能，这样。这仍然是最好的方法，或者是有一个简单的方法来访问这个功能吗？

DB::connection()->getPdo()->quote("string to quote");

Possible duplicate of How can I prevent SQL-injection in PHP? — Epodax, May 31 '16 at 7:50
Just to ask, why do you use raw SQL instead of the eloquent model? — TheFallen, May 31 '16 at 8:33
感谢1包括解（你也可以发布它的功能一个答案，并接受它…可以节省一些时间给我们） — J.布吕尼, 14年4月7日凌晨0时5分
@j.bruni，好点！我现在把它添加为一个实际的答案，所以应该很容易找到。 — 德怀特, 14年4月22日在0:36

Filip Koblański · Accepted Answer · 2016-06-01 06:43:25Z

If you need to bind some data then try like this (according to the docs):

DB::insert(
    'INSERT IGNORE INTO classrooms_subjects (`classroom_id`,`subject_id`) VALUES (?, ?)',
     [$classroomId, $subjectId]
);

我的方法

$user=\DB::connection('mysqllocalhost')->select("select * from users where email=?",array($email));

You can pass data with array as a secound parameter here.

德怀特 · Accepted Answer · 2014-04-22 00:35:38Z

你可以给你的字符串，这样，通过DB外观.

DB::connection()->getPdo()->quote("string to quote");

我的方法是

$email = \DB::connection('mysqllocalhost')->getPdo()->quote($email); //var_dump($email); $user=\DB::connection('mysqllocalhost')->select('select * from users where email='.$email)[0];

我把这个回答我的问题时，我发现了它，但是现在我已经把它放在使别人更容易找到真实的答案。

谢谢!这是多么惊人的人回答错了问题坚决。我需要同样的回答你，和参数不在我的处境。 — 加布里埃尔Magana, 14年4月28日在23:16

阿伦库马尔M · Answer 3 · 2013-09-23 02:49:56Z

了投票十七投票表决

$value = Input::get("userID");

$results = DB::select( DB::raw("SELECT * FROM users WHERE users.id = :value"), array(
   'value' => $value,
 ));

更多细节酒店雇员和饭馆雇员

回答13年9月23日凌晨2时49分

阿伦库马尔M

一千一百八十三七十六

二

这并没有回答这个问题，它说，我不是寻找解决而是逃避的占位符变量的适当方式。–德怀特13年9月24日在1:24

一

@德怀特：是模拟。当你逃避实际编码的变量值为SQL，那么占位符。那么PDO。–hakre14年5月11日上午8时49分

一

当心，我有问题用这种结合where声明.查询生成器不会将占位符在select()和其他动态替换如在where–tomvo7月22 15 15：22

“hakre如果我需要逃避一堆（变量数）变量放入一个“（）”的声明中的一部分吗？–āNIS elmeris [J].在12月4 16 17点

一

“Janis：你发现这样的问题的回答我可以把数组的in()条件？这是妹妹的问答–hakre16年12月5日在11:29

添加评论

阿尔法 · Answer 4 · 2013-09-23 02:54:29Z

了投票七投票表决

你也可以试试这个，（读文件）

$results = DB::select('SELECT * FROM users WHERE users.id = ?', array($userId));

回答13年9月23日在2:54

阿尔法

94.6k十五一百五十九二百零六

二

1这个答案。使用数组绑定，像上面的例子，会保护你。进一步的信息在这里forums.laravel.io/viewtopic.php?id=11068–劳伦斯9月23日13，因为

这并没有回答这个问题，它说，我不是寻找解决而是逃避的占位符变量的适当方式。–德怀特13年9月24日在1:22

一

“德怀特，这是什么array binding/bound params确实，安全逃脱，没有SQL注入可以应用不仅占位符。刚读这个答案要明确自己–阿尔法13年9月24日在1:35

“德怀特，也注意到第一个评论，它没有告诉你吗？–阿尔法13年9月24日凌晨1时39分

添加评论

J.布吕尼 · Answer 5 · 2015-06-08 20:41:26Z

我用在我的helpers.php5在laravel：

if ( ! function_exists('esc_sql'))
{
    function esc_sql($string)
    {
        return app('db')->getPdo()->quote($string);
    }
}

然后我可以使用esc_sql我pergorm«逃脱need to function for Raw SQL查询。

拉尔夫克 · Answer 6 · 2015-07-27 13:36:52Z

我发现这个问题的时候，寻找通用的SQL逃离Laravel。我真的需要虽然是表/列名逃逸。因此，作为未来的参考：

/**
 * Quotes database identifier, e.g. table name or column name. 
 * For instance:
 * tablename -> `tablename`
 * @param  string $field 
 * @return string      
 */
function db_quote_identifier($field) {
  static $grammar = false;
  if (!$grammar) {
    $grammar = DB::table('x')->getGrammar(); // The table name doesn't matter.
  }
  return $grammar->wrap($field);
}

You are here

Escape raw SQL queries in Laravel

How to use safe raw queries against sql injections in laravel4 for INSERT IGNORE

1 Answer 正确答案

来自 https://stackoverflow.com/questions/37539763/how-to-use-safe-raw-queries-against-sql-injections-in-l...

Raw Queries in Laravel

5个答案正确答案

来自 https://stackoverflow.com/questions/18951057/escape-raw-sql-queries-in-laravel-4

友情链接

搜索表单

用户登录

You are here

Escape raw SQL queries in Laravel

How to use safe raw queries against sql injections in laravel4 for INSERT IGNORE

1 Answer 正确答案

来自 https://stackoverflow.com/questions/37539763/how-to-use-safe-raw-queries-against-sql-injections-in-l...

Raw Queries in Laravel

5个答案 正确答案

来自 https://stackoverflow.com/questions/18951057/escape-raw-sql-queries-in-laravel-4

友情链接

5个答案正确答案