数据库::语句(); 在 Laravel (5.5)

DB::statement(); in Laravel (5.5)

提问by Marin Leontenko

I'm building an application with Laravel 5.5 and I have to run some SQL queries for extended PostgreSQL which are not supported by Eloquent ORM (PostGIS). Instead, I use DB::statement($queryString); to insert data into database. $queryString is built from input variables concatenated with pre-built SQL statement.

我正在使用 Laravel 5.5 构建一个应用程序，我必须为扩展的 PostgreSQL 运行一些 SQL 查询，而 Eloquent ORM (PostGIS) 不支持这些查询。相反，我使用 DB::statement($queryString); 将数据插入数据库。$queryString 由输入变量与预构建的 SQL 语句连接而成。

Here is the code from my controler (note that actual query is more complex, this is just an example):

这是我的控制器的代码（注意实际查询更复杂，这只是一个例子）：

/**
 * Store a newly created resource in storage.
 *
 * @param  \Illuminate\Http\Request  $request
 * @return \Illuminate\Http\Response
 */
public function store(Request $request)
{   
    $id= $request->input('id');
    $name= $request->input('name');
    $geom = $request->input('geom');

    $geom = DB::raw("ST_TRANSFORM(ST_GeomFromGeoJSON('".$geom."'), 3857)");


    $statement = "INSERT INTO tableName(id, name) VALUES ('".$id."', '".$name."', ".$geom.");";
    DB::statement($statement);
    return 'Insert Successful';
}

I have two question about this approach:

我对这种方法有两个问题：

1. How can I protect my application from SQL injection attacks?

2. How can I check if query ran successfully? DB::statement doesn't seem to return anything.

1. 如何保护我的应用程序免受 SQL 注入攻击？

2. 如何检查查询是否成功运行？DB::statement 似乎没有返回任何东西。

回答by

1, use parameter bindings to safeguard your queries. Example:

1、使用参数绑定来保护您的查询。例子：

$users = DB::select('select * from users where active = ?', [1]);

https://laravel.com/docs/5.5/database#running-queries

https://laravel.com/docs/5.5/database#running-queries

As for #2, wrap your queries in database transactions. That will protect against failures. Example:

至于#2，将您的查询包装在数据库事务中。这将防止故障。例子：

DB::transaction(function () {
    DB::table('users')->update(['votes' => 1]);

    DB::table('posts')->delete();
});

回答by Ahmed Shams

you can make this after importing

你可以在导入后做这个

use Illuminate\Support\Facades\DB;

you can right your query like that

你可以像这样纠正你的查询

$quert=DB::insert('insert into tablename (columnname1,columnname2,) values (?,?)',[$id,$username]);

and if you wanna insert whole data to table but when is there is no data in table except id and user name

如果你想将整个数据插入表但是什么时候表中除了 id 和用户名之外没有数据

$quert=DB::insert('insert into tablename values (?,?)',[$id,$username]);

the question mark is question mark ? :D dont put any input here and you are welcome

问号是问号？:D 不要在这里输入任何内容，欢迎您

edited:

编辑：

about How can I protect my application from SQL injection attacks? you can make this from middlewares ,using Cross-Site Scripting like

关于如何保护我的应用程序免受 SQL 注入攻击？你可以从中间件中做到这一点，使用跨站点脚本，如

{% raw %} {{}} {% endraw %}

with CSRF

与CSRF

<form ...>
{!! csrf_field() !!}
</form>

for second question you have multi thing

对于第二个问题，你有很多事情

1- easy way

1-简单的方法

you can return your query if you select it will return selection if it inserted/updated/deleted it will be true if not it will be false

你可以返回你的查询，如果你选择它会返回选择如果它插入/更新/删除它会是真的，如果不是它会是假的

                return response()->json([
                'Message'=>'what ever you wanna write',
                'Data' => $query,
                 ], 200);

2- hard way(i prefered :D)

2-艰难的方式（我更喜欢：D）

you can use DB::listen for debugging

您可以使用 DB::listen 进行调试

DB::listen(function ($query) {
// $query->sql
// $query->bindings
// $query->time
});

回答by RoboBear

In general, according to OWASP, there are four ways to protect against SQL injection.

一般来说，根据OWASP，有四种方法可以防止 SQL 注入。

1. Prepared Statements
2. Stored Procedures
3. White list/ input validation
4. Escape ALL user supplied input

1. 准备好的报表
2. 存储过程
3. 白名单/输入验证
4. 转义所有用户提供的输入

In your case, for Laravel 5+ where the Eloquent ORM will not work directly, I think the best option is #1, Prepared Statements.

在您的情况下，对于 Eloquent ORM 不能直接工作的 Laravel 5+，我认为最好的选择是 #1, Prepared Statements。

More specifically, in Laravel you can achieve it's built in Query Builderto either iteratively build statements or execute a completely raw SQL statement with

更具体地说，在 Laravel 中，您可以实现它内置于Query Builder以迭代地构建语句或执行完全原始的 SQL 语句

DB:: select ( [raw query string] )

SOURCE

来源

SO Post: Laravel, execute raw queries

SO Post：Laravel，执行原始查询