Error when making a query with PDO

Question

Error when making a query with PDO

Navigation

#1 by (1 votes)

1

Fatal error: Uncaught PDOException: 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 '' inventory 'WHERE' Inv_User '=' gmarsi '' at line 1 in C: \ xampp \ htdocs \ qbc_web \ functions \ functions_contador.php: 14 Stack trace: # 0 C: \ xampp \ htdocs \ qbc_web \ functions \ functions_contador.php (14): PDOStatement-> execute () # 1 C: \ xampp \ htdocs \ qbc_web \ login \ profile.php (75): Count Elements ('Inv_User', 'inventory', 'Inv_User', 'gmarsi') # 2 {main} thrown in C: \ xampp \ htdocs \ qbc_web \ functions \ functions_contador.php on line 14

I get this error when generating the query, the code is this:

function contarElementos($element, $table, $condition, $purchased){    
        require(realpath($_SERVER["DOCUMENT_ROOT"]) .'/qbc_web/datos_conexion.php');         

    $consulta = "SELECT COUNT(:element) AS Contador FROM :table WHERE :condition = :purchased";

    $result = $base -> prepare($consulta);

    $result -> bindValue(':element', $element);
    $result -> bindValue(':table', $table);
    $result -> bindValue(':condition', $condition);
    $result -> bindValue(':purchased', $purchased);
    $result -> execute();
    $sql_contador = $result->rowCount();


    if($sql_contador < 1){
            return 0;
    }else{
       while($fila = $result -> fetch(PDO::FETCH_ASSOC)){ 
            return $fila['Contador'];
        }
    }    
    // echo $consulta;
}

The function basically what it does is to count the elements of X table taking into account the parameters that are introduced. As you can see in the error appears contarElementos('Inv_User', 'inventory', 'Inv_User', 'gmarsi') which are the parameters that I want the function to use to query the database. Why is this?

php pdo

asked by gmarsi 31.08.2017 в 19:35

source

1 answer

How to extract the numeric IMEI as shown on the phone when doing * # 06 # from my application [duplicated] Div with position: relative overlays another with position: fixed

score 1 · Accepted Answer

Table names and column names can not be passed in parameters when using prepared queries, as in this case using PDO.

You can see it in the MySQL documentation :

... Within the declaration, the ? characters can be used as parameter markers to indicate where the links will be linked Data values to the query later when it is executed. The characters ? should not be enclosed in quotes, even if you have the intent to link them to string values. The bookmarks of parameters can only be used in the data values that should be appear, not for SQL keywords, identifiers (names of tables, columns ...), etc. .

The above statement also applies to the :nombre markers.

Therefore, this is wrong:

SELECT COUNT (: element) AS Counter FROM: table WHERE: condition ...

At most it should be something like:

"SELECT COUNT($element) AS Contador FROM $table WHERE $condition ..."

saved to pass the values in parameters, that is the most important thing in the face of SQL Injection:

$ result - > bindValue (': element', $ element);

$ result - > bindValue (': table', $ table);

$ result - > bindValue (': condition', $ condition);

//Este es el valor importante para pasar en parámetro
$result -> bindValue(':purchased', $purchased);

On the other hand, the use of rowCount to know if there is data is a myth widespread in PDO.

If you want to know if there is data, act on the same data, so you avoid the rowCount supplementary, optimizing the code. For example:

$ sql_counter = $ result->> rowCount ();

$fila=$result -> fetch(PDO::FETCH_ASSOC);

//Si no hay datos $fila será 'false' 
if(! $fila){
        return 0;
}else{
   while($fila){ 
        return $fila['Contador'];
    }
}