Syntax error using MySQL variable in Laravel

Question

I'm trying to run the following raw query from Laravel:

DB::statement(
    DB::raw("
    SET @lon = ?;
    SET @lat = ?;
    "),
    [$longitude, $latitude],
);
$places = DB::select(
    DB::raw("
    SELECT
    id
    FROM places
    WHERE
          position = POINT(@lon, @lat)
    AND
          language = ?
    AND
          LOWER(name) LIKE LOWER(CONCAT(?, '%'))
    ORDER BY distance
    LIMIT 10;
    "),
    [
        $language,
        $name,
    ],
);

The actual query is more complex but it's beyond the scope of the question. Suffice to say that I need @lon and @lat to be SQL variables.

This gives me the following error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @lat = ?' at line 2 (SQL: \n SET @lon = 7.4653;\n SET @lat = 51.5136;\n )

I had initially tried to put the SET statements inside the same string as the query, but some answers on stack overflow suggested they should be separated. Unfortunately that didn't work, but as far as I can see there isn't any actual error in the syntax, so what is going on?

what is you MySQL version pls ?

Hicham O'Sfh
– Hicham O'Sfh

2021-05-28 12:26:07 +00:00
Commented May 28, 2021 at 12:26 — Hicham O'Sfh
– Hicham O'Sfh, Commented May 28, 2021 at 12:26
@HichamO-Sfh 5.7.31.

theberzi
– theberzi

2021-05-28 12:28:01 +00:00
Commented May 28, 2021 at 12:28 — theberzi
– theberzi, Commented May 28, 2021 at 12:28

Hicham O'Sfh · Accepted Answer · 2021-05-28 12:51:20Z

1

According to Laravel Official Documentation, DB:raw doesn't prevent SQL injection attack so it's not the recommended way what to use it in your case even if you're using SQL parameters/variables...
However I think that neither DB::statement nor DB::raw does not support multiple queries, so you should separate them :

// still a 'risky code' :
DB::statement(DB::raw("SET @lon = ?;",[$longitude]));
DB::statement(DB::raw("SET @lat = ?;",[$latitude]));

Still the best practice is to use named bindings

Edit
If you still really need to use DB::raw and DB::statement , then here is a really secured way to use them (combining them with the named binding method ) :

// safe code (said by Laravel) :
DB::statement(DB::raw("SET @lon = :longitude;"), ['longitude'=>$longitude]);
DB::statement(DB::raw("SET @lat = :latitude;"), ['latitude'=>$latitude]);

edited May 28, 2021 at 12:51

answered May 28, 2021 at 11:14

Hicham O'Sfh

84912 silver badges14 bronze badges

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

9 Comments

theberzi Over a year ago

Noted. Is there an easy way to secure the query then?

Hicham O'Sfh Over a year ago

yes of course as I've already said, the best practice is to use prepared parameters & named bindings, I've posted direct links to the documentation. glad to help you.

theberzi Over a year ago

I have replaced my bindings to be named, but is that enough to prevent SQL injection if $longitude and $latitude come from the request?

Hicham O'Sfh Over a year ago

@theberzi I'm not really sur about that, but I have already seen something related to preventing SQLi even when using DB::raw : laracasts.com/discuss/channels/laravel/…

Hicham O'Sfh Over a year ago

@theberzi I have updated my answer, take a look pls.

|

Collectives™ on Stack Overflow

Syntax error using MySQL variable in Laravel

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related