Error executing prepared query

3
$user = $_COOKIE['c_username'];       
$filter = filter_var($_POST['buscar_inventario'], FILTER_SANITIZE_STRING);

$query = "SELECT 
                     Inv_Id, 
                     Inv_Ref, 
                     Inv_Name, 
                     Inv_Desc, 
                     Inv_Purch_Price, 
                     Inv_Sale_Price, 
                     Inv_Quant, 
                     Inv_Date, 
                     Inv_Public 
                 FROM inventory 
                 WHERE 
                     Inv_User = :user 
                     AND Inv_Name LIKE %:filter% 
                     OR Inv_Desc LIKE %:filter% 
                     OR Inv_Ref LIKE %:filter% 
                     OR Inv_Purch_Price LIKE %:filter% 
                     OR Inv_Sale_Price LIKE %:filter% 
                 ORDER BY Inv_Ref ASC 
                 LIMIT 20";

$result = $base -> prepare($query);
$result -> bindValue(':user', $user);
$result -> bindValue(':filter', $filter);
$result -> execute();

The error that generates me is the following:

  

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 '%' product '% OR Inv_Desc LIKE%' product '% OR Inv_Ref LIKE%' product '% OR Inv_P' at line 1

Is it possible that I get the error by using %:filter% ?

    
asked by gmarsi 26.09.2017 в 10:47
source

2 answers

2

Prepared queries do not support the use of named variables as part of a string, you must use one of the following solutions:

PHP Function PDO::quote

With this solution we will escape the content of the variable $filter to insert it directly in the SQL query:

/* Aquí debes usar tu instancia de conexión PDO */
$filter = '%' . $pdo->quote(
  filter_var($_POST['buscar_inventario'], FILTER_SANITIZE_STRING)
) . '%';

$query = "
  SELECT
    Inv_Id,
    Inv_Ref,
    Inv_Name,
    Inv_Desc,
    Inv_Purch_Price,
    Inv_Sale_Price,
    Inv_Quant,
    Inv_Date,
    Inv_Public
  FROM inventory
  WHERE
    Inv_User = :user
  AND
    (
      Inv_Name LIKE $filter
    OR
      Inv_Desc LIKE $filter
    OR
      Inv_Ref LIKE $filter
    OR
      Inv_Purch_Price LIKE $filter
    OR
      Inv_Sale_Price LIKE $filter
    )
  ORDER BY Inv_Ref ASC LIMIT 20
";

$result = $base->prepare($query);
$result->bindValue(':user', $_COOKIE['c_username']);
$result->execute();

Note: The function adds the external quotes.

SQL function CONCAT

This solution makes use of the SQL function CONCAT to mount the string that will be compared using the operator LIKE :

$query = "
  SELECT
    Inv_Id,
    Inv_Ref,
    Inv_Name,
    Inv_Desc,
    Inv_Purch_Price,
    Inv_Sale_Price,
    Inv_Quant,
    Inv_Date,
    Inv_Public
  FROM inventory
  WHERE
    Inv_User = :user
  AND
    (
      Inv_Name LIKE CONCAT('%', :filter, '%')
    OR
      Inv_Desc LIKE CONCAT('%', :filter, '%')
    OR
      Inv_Ref LIKE CONCAT('%', :filter, '%')
    OR
      Inv_Purch_Price LIKE CONCAT('%', :filter, '%')
    OR
      Inv_Sale_Price LIKE CONCAT('%', :filter, '%')
    )
  ORDER BY Inv_Ref ASC LIMIT 20
";

$result = $base->prepare($query);
$result->bindValue(':user', $_COOKIE['c_username']);
$result->bindValue(
  ':filter',
  filter_var($_POST['buscar_inventario'], FILTER_SANITIZE_STRING)
);
$result->execute();

Include wildcards in the value

With this solution we add the pattern within the variable assigned to the SQL:

$query = "
  SELECT
    Inv_Id,
    Inv_Ref,
    Inv_Name,
    Inv_Desc,
    Inv_Purch_Price,
    Inv_Sale_Price,
    Inv_Quant,
    Inv_Date,
    Inv_Public
  FROM inventory
  WHERE
    Inv_User = :user
  AND
    (
      Inv_Name LIKE :filter
    OR
      Inv_Desc LIKE :filter
    OR
      Inv_Ref LIKE :filter
    OR
      Inv_Purch_Price LIKE :filter
    OR
      Inv_Sale_Price LIKE :filter
    )
  ORDER BY Inv_Ref ASC LIMIT 20
";

$result = $base->prepare($query);
$result->bindValue(':user', $_COOKIE['c_username']);
$result->bindValue(
  ':filter',
  '%' . $pdo->quote(
    filter_var($_POST['buscar_inventario'], FILTER_SANITIZE_STRING)
  ) . '%'
);
$result->execute();

Note: Thanks @Pikoh for the hint.

    
answered by 26.09.2017 / 10:56
source
3

The problem is that :filter is added as a string to the query, with its single quotes. That way the part of the like is like %'tufiltro'% , which is not correct. If you concatenate the wildcards in your filter and remove them from the query, it will work correctly:

$user = $_COOKIE['c_username'];       
$filter = '%' + filter_var($_POST['buscar_inventario'], FILTER_SANITIZE_STRING) + '%';

$query = "SELECT Inv_Id, Inv_Ref, Inv_Name, Inv_Desc, Inv_Purch_Price, Inv_Sale_Price, Inv_Quant, Inv_Date, Inv_Public FROM inventory WHERE Inv_User = :user AND Inv_Name LIKE :filter OR Inv_Desc LIKE :filter OR Inv_Ref LIKE :filter OR Inv_Purch_Price LIKE :filter OR Inv_Sale_Price LIKE :filter ORDER BY Inv_Ref ASC LIMIT 20";

$result = $base -> prepare($query);
$result -> bindValue(':user', $user);
$result -> bindValue(':filter', $filter);
$result -> execute();
    
answered by 26.09.2017 в 12:41