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

Question

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.

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:

How can I protect my application from SQL injection attacks?
How can I check if query ran successfully? DB::statement doesn't seem to return anything.

there are some examples in the Laravel docs. https://laravel.com/docs/5.5/database#running-queries — Wreigh, Feb 05 '18 at 08:24
Exactly @pankajMakwana is right why are you using query to insert data. — Mohsin Abbas, Feb 05 '18 at 08:24
[Use Eloquent where possible](https://github.com/alexeymezenin/laravel-best-practices#prefer-to-use-eloquent-over-using-query-builder-and-raw-sql-queries-prefer-collections-over-arrays) to keep the code safe and maintainable. Look at the examples in my repo, do you still want to use raw queries? — Alexey Mezenin, Feb 05 '18 at 08:25
Check this link https://laravel.com/docs/5.5/database. Use this function `DB::insert()` — Pankaj Makwana, Feb 05 '18 at 08:25
I used example with id and name for the sake of simplicity, but I added geometry to the code now. I need to insert PostGIS geometry, I tried to use DB::insert(), but it doesn't work so I had to use DB::statement(); (which worked) — Marin Leontenko, Feb 05 '18 at 08:43
Is there a way to filter the variables from request ($id, $name, $geom) for SQL? That would be enough for question #1. — Marin Leontenko, Feb 05 '18 at 09:01

Ahmed Shams · Accepted Answer · 2018-02-05T08:47:01.273

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

$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

edited:

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

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

with CSRF

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

for second question you have multi thing

1- easy way

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)

you can use DB::listen for debugging

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

score 2 · Answer 2 · 2018-02-05T08:35:37.683

2

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

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

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

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

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

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

edited Feb 05 '18 at 08:35

answered Feb 05 '18 at 08:29

Thanks, I have always thought parameter bindings is safe to sql injection. I am thinking about using prepared statements which although safe is tedious. So if parameter bindings is sufficient for providing safeguard, I will use it instead of tedious prepared statements. – Lex Soft Feb 11 '20 at 04:12

score 2 · Answer 3 · answered Feb 05 '18 at 09:22

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

Prepared Statements
Stored Procedures
White list/ input validation
Escape ALL user supplied input

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

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

DB:: select ( [raw query string] )

SOURCE

SO Post: Laravel, execute raw queries

So parameter bindings used in normal DB:insert doesn't give protection to SQL injection ? In other words it doesn't implement prepared statements internally ? I tried to use DB::statement for prepared statements, but doesn't work. Then I tried DB::raw to execute my prepared statements, and it worked. — Lex Soft, Feb 11 '20 at 04:07

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

3 Answers3