Concatenate a PHP variable in a MYSQL query

Question

Concatenate a PHP variable in a MYSQL query

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

I'm doing a query SQL in PHP using MVC . I'm doing a function which has a parameter and this is used in the query SQL . This is the code and up to this point it works for me.

public function miFuncion($u){
  $stm = $this->pdo->prepare("SELECT T.tare_id AS tarea_id, TI.tick_codi AS codigo, TI.tick_nom AS nombre_ticket, PI.piez_nom AS pieza, T.num_pieza AS cantidad_piezas, PR.prod_nom AS producto,concat(US.usua_nom,' ',US.usua_ape) AS usuario , ES.esta_nom AS estado, EM.empr_nom AS cuenta, T.tare_asig AS fecha_de_asignacion, T.tare_entr AS fecha_de_entrega, MAX(HI.hist_cod) AS cantidad_ajustes
                                    FROM tarea T
                                    INNER JOIN pieza PI ON T.piez_id = PI.piez_id
                                    INNER JOIN producto PR ON T.prod_id = PR.prod_id
                                    INNER JOIN usuario US ON T.user_id = US.usua_id
                                    INNER JOIN ticket TI ON T.tick_id = TI.tick_id 
                                    INNER JOIN estados ES ON T.esta_id = ES.esta_id
                                    INNER JOIN empresa EM ON TI.empr_id = EM.empr_id
                                    INNER JOIN historial HI ON T.tare_id = HI.tare_id
                                    WHERE T.user_id = 11
                                    GROUP BY T.tare_id
                                    ORDER BY tarea_id ASC");
  $stm->execute();
  return $stm->fetchAll(PDO::FETCH_OBJ);

}

When I replace in the line of WHERE the value of 11 by the variable $u This throws 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 MariaDB server version for the right syntax to use near 'GROUP BY T.tare_id ORDER BY ASC_work ASID' at line 11

The value of the parameter is reaching the function, I have checked it with an'echo'. But when replacing the value of 11 with the variable $ u the query does not work for me anymore.

mysql php mvc pdo

asked by Carlos Cespedes 04.01.2019 в 16:52

source

2 answers

1

This error 1064 is presented by several causes, among them and more common is to use a reserved word.

Also for misusing a command such as writing FRON (when you wanted to type FROM ) or UDDATE < em> (when you wanted to type UPDATE ) , but if it works with the 11 burned, this is not the case.

Try renaming the parameter with: T.user_id = $ id_user or in single quotes T.user_id = '$ id_user'

answered by 04.01.2019 в 17:10

How to make the fields in a table fill by default with values from another table? Obtain the object of a foreign key in Django

score 2 · Accepted Answer

If you are using PDO it is not correct and if you are very insecure that you pass the variable directly to the query, you should instead pass the value as a name tag > in this form :valor

Then you should have it like this, for example:

$u = 10;

Now within your query, send it as:

$stm = $this->pdo->prepare("SELECT T.tare_id AS tarea_id, TI.tick_codi AS codigo, TI.tick_nom AS nombre_ticket, PI.piez_nom AS pieza, T.num_pieza AS cantidad_piezas, PR.prod_nom AS producto,concat(US.usua_nom,' ',US.usua_ape) AS usuario , ES.esta_nom AS estado, EM.empr_nom AS cuenta, T.tare_asig AS fecha_de_asignacion, T.tare_entr AS fecha_de_entrega, MAX(HI.hist_cod) AS cantidad_ajustes
                                    FROM tarea T
                                    INNER JOIN pieza PI ON T.piez_id = PI.piez_id
                                    INNER JOIN producto PR ON T.prod_id = PR.prod_id
                                    INNER JOIN usuario US ON T.user_id = US.usua_id
                                    INNER JOIN ticket TI ON T.tick_id = TI.tick_id 
                                    INNER JOIN estados ES ON T.esta_id = ES.esta_id
                                    INNER JOIN empresa EM ON TI.empr_id = EM.empr_id
                                    INNER JOIN historial HI ON T.tare_id = HI.tare_id
                                    WHERE T.user_id = :u
                                    GROUP BY T.tare_id
                                    ORDER BY tarea_id ASC");

Later we use bindParam() to indicate the value by which the name marker should be replaced, in this way

$stm->bindParam(':u', $u);

In this way you help minimize SQL injection attacks because you do not pass directly the value that comes from the user to your SQL query

Optionally you can indicate in the bindParam() the type of data that you expect to receive, having options such as:

PDO::PARAM_INT For integer values

PDO::PARAM_STR For values of type text string

Staying this way

$stm->bindParam(':u', $u, PDO::PARAM_INT);