SQLSTATE [42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax

Question

SQLSTATE [42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax

Navigation

#1 by (0 votes)

0

I am developing a API with slim framework 3 that connects to a base de datos local. The problem is the following when I try to update an element of my BD by passing it a id said procedure returns the following error "{" error ": {" text ": SQLSTATE [42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where idService = 1' at line 2} "

specialty.php

    <?php

    use \Psr\Http\Message\ServerRequestInterface as Request;
    use \Psr\Http\Message\ResponseInterface as Response;

$app->group('/especialidad/', function () {
    $this-> put('actualizar/{id}', function(Request $request, Response $response){

    $id = $request->getAttribute('id');

    $nombre = $request->getParam('nombre');


    $sql = "update especialidad set nombre =:nombre ,
            where idEspecialidad = $id"; 

    try{
        $db = new db();
        $db = $db->connect();

        $stmt = $db->prepare($sql);
        $stmt->bindParam(':nombre', $_POST['nombre'], PDO::PARAM_STR);
        $stmt->execute();
        $db = null;

        echo '{"notice": {"text": "Especialidad Actualizada"}';

    } catch(PDOException $e){
        echo '{"error": {"text": '.$e->getMessage(). '}';
    }

});

});

api pdo rest

asked by Germanccho 27.01.2018 в 20:09

source

1 answer

HTML Trusted or HTML Safe Create table with Fk

score 0 · Answer 1

The error is due to incorrect syntax here:

update especialidad set nombre =:nombre , where idEspecialidad = $id

There is a , before where .

Now, you have a serious security problem, passing the $id value directly. That value must also be passed separately, as you do with the value :nombre .

As an additional fact, it is totally valid to pass the parameters in execute , without having to resort to bindParam , so you will see that I have created a $arrParams , in which I create the parameters and then I pass them in the execute . It will save you some lines of code, especially in queries with many parameters.

Here's the corrected code:

    <?php

    use \Psr\Http\Message\ServerRequestInterface as Request;
    use \Psr\Http\Message\ResponseInterface as Response;

$app->group('/especialidad/', function () {
    $this-> put('actualizar/{id}', function(Request $request, Response $response){

    $id = $request->getAttribute('id');

    $nombre = $request->getParam('nombre');


    $sql = "update especialidad set nombre =:nombre 
            where idEspecialidad =:id"; 

    try{
        $db = new db();
        $db = $db->connect();
        $arrParams=array(':nombre'=>$_POST['nombre'], ':id'=>$id);
        $stmt = $db->prepare($sql);
        $stmt->execute($arrParams);
        $db = null;

        echo '{"notice": {"text": "Especialidad Actualizada"}';

    } catch(PDOException $e){
        echo '{"error": {"text": '.$e->getMessage(). '}';
    }

});

});