CODEWITHSUNDEEP
sqllaravel
Arminius
Arminius

Reputation: 606

Securing a raw sql query in Laravel to prevent injections

The query when not trying any SQL injection fix works perfectly there is no any connection issue. It is only when trying to change the query to protect it from injections, when the syntax breaks.

I am trying to secure this raw query preventing sql injection

I have seen in the docs (and I know the prepared statements from goo'ol php) and that Laravel shows a simple example such as this one:

$results = DB::select('select * from users where id = :id', ['id' => 1]);

or this one

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

I am trying to do that where it corresponds, at the WHERE clause equaling the variable provided from the form $a, but all attempts break the syntax.

so anything like:

where author = ?, [$ba], or '$ba' or where author = :ba, ['author' => '$ba']

gives syntax errors. 

    $ba = 'whatever';    
    $results = 
DB::select(DB::raw("SELECT 
          t.id, t.AvgStyle, r.RateDesc
          FROM (
               SELECT
               p.id, ROUND(AVG(s.Value)) AS AvgStyle
               FROM posts p
               INNER JOIN styles s
               ON s.post_id = p.id  
               WHERE author = '$ba'    
               GROUP BY p.id
               ) t
               INNER JOIN rates r
                   ON r.digit = t.AvgStyle"
                        ));

Thank you. Note> question is not eligible for bounties. It is someone always putting it on every question of mine without my permission.

Upvotes: 1

Views: 2340

Answers (2)

Everton Mendonça
Everton Mendonça

Reputation: 688

You can try this way:

$ba = 'whatever';    
    $results = 
DB::select(DB::raw("SELECT 
          t.id, t.AvgStyle, r.RateDesc
          FROM (
               SELECT
               p.id, ROUND(AVG(s.Value)) AS AvgStyle
               FROM posts p
               INNER JOIN styles s
               ON s.post_id = p.id  
               WHERE author = ?    
               GROUP BY p.id
               ) t
               INNER JOIN rates r
                   ON r.digit = t.AvgStyle"
          , )[$ba]); // @everton We just needed to move it one parentheses

The important thing here is that you need to respect the order if you have others parameters.

Upvotes: 2

Arminius
Arminius

Reputation: 606

The answer was extremely close to what Everton said: It just needed to go one parentheses to the right.

That is:

, )[$ba]);

Upvotes: 3

Related Questions