Laravel Lumen : execute mysql function with insert into

Question

I'm looking to execute a custom MySQL function in a query in order to update (or insert) a value in database.

My actual code (with custom query) :

DB::statement('INSERT INTO locations(longitude, latitude, altitude, speed, point, user_id, circle)
                    VALUES (' . $long . ', ' . $lat . ', ' . $alt . ', ' . $speed . ', ' . $point . ', GETPOLYGON(' . $lat . ', ' . $long . ' , 0.300, 12))
                    ON DUPLICATE KEY UPDATE longitude=' . $long . ',latitude=' . $lat . ',altitude=' . $alt . ',speed=' . $speed . ',point=' . $point . ',circle=GETPOLYGON(' . $lat . ', ' . $long . ' , 0.300, 12)');

I don't really know if execute like this is the best way in my case.

My custom function is 'getpolygon'. But it doesn't seem to work

[2020-04-17 08:23:01] local.ERROR: PDOException: 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 '43.325178, GETPOLYGON(43.325178, 1.121354 , 0.300, 12))

My function in SQL :

CREATE FUNCTION getpolygon(lat DOUBLE, lon DOUBLE, radius SMALLINT, corner TINYINT) RETURNS geometry DETERMINISTIC BEGIN DECLARE i TINYINT DEFAULT 1; DECLARE a, b, c DOUBLE; DECLARE res TEXT; IF corner < 3 || radius > 500 THEN RETURN NULL; END IF; SET res = CONCAT(lat + radius / 111.12, ' ', lon, ','); WHILE i < corner  do SET c = RADIANS(360 / corner * i); SET a = lat + COS(c) * radius / 111.12; SET b = lon + SIN(c) * radius / (COS(RADIANS(lat + COS(c) * radius / 111.12 / 111.12)) * 111.12); SET res = CONCAT(res, a, ' ', b, ','); SET i = i + 1; END WHILE; RETURN GEOMFROMTEXT(CONCAT('POLYGON((', res, lat + radius / 111.12, ' ', lon, '))')); END;

Thanks

Answer 1

There's a few errors here as commented in the question.

1. Your function expects radius to be SMALLINT, but you are passing a float value to it.
2. You are missing user_id in values, so the actual code puts the output of your function getpolygon() to user_id column, and nothing into circle column, leading to a syntax error.
3. Your code is probably vulnerable to SQL Injection. I said probably because I don't know the source of the variables that you concatenate on the query.

So, to fix the SQL Injection, you can use prepared statements with DB::statement($query, $bindings) (see API Docs):

DB::statement(
    'INSERT INTO locations(longitude, latitude, altitude, speed, point, user_id, circle)
     VALUES (?, ?, ?, ?, ?, ?, GETPOLYGON(?,?, 0.300, 12))
     ON DUPLICATE KEY UPDATE longitude=?,latitude=?,altitude=?,speed=?,point=?,user_id=?,circle=GETPOLYGON(?,?, 0.300, 12)',
    [
        $long, $lat, $alt, $speed, $point, $user_id, $lat, $long,
        $long, $lat, $alt, $speed, $point, $user_id, $lat, $long,
    ]
);

Note that the params are added twice since you need to bind into insert and update statements.