Connection failed: SQLSTATE[42000]: Syntax error or access violation: 1064

Question

I've already seen a lot of questions similar to mine but mine is different: My error is not caused by the use of a "keyword" in SQL!

when I run my code, if I cancel the WHERE clause or I use an hard coded value such as " WHERE customerNumber = 356" it works fine but if I try to use the value of the variable $custom_n, it throw an error.

<?php 
            $custom_n = $_POST["emp"];
            $servername = "localhost";
            $username = "root";
            $password = "";

            try {
                $conn = new PDO("mysql:host=$servername;dbname=classicmodels", $username, $password);
                $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

                $stmt = $conn -> prepare("SELECT phone, salesRepEmployeeNumber, creditLimit FROM customers WHERE customerNumber = $custom_n");
                $stmt -> execute();
                $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); 
                foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) { 
                    echo $v;
                }
            }
            catch(PDOException $e) {
                echo "Connection failed: " . $e->getMessage();
            }

            $conn = null;

        ?>

If i do an echo to custom_n and gettype i get: 353 string which is what i wanted.

This is the full error:

Connection failed: 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 '' at line 1

As suggested by Magnus Eriksson, If I do a var_dump($custom_n); i get this output: string(12) ""

Answer 1

$stmt = $conn -> prepare("SELECT phone, salesRepEmployeeNumber, creditLimit FROM customers WHERE customerNumber = ?");
$stmt->execute(array($custom_n));

Your code seems fine so i assume your string escapes the query. Try to use prepared statements next time.

Answer 2

Preferred way is binding. You can bind with param like this. You can refer to here

$stmt = $conn -> prepare("SELECT phone, salesRepEmployeeNumber, creditLimit FROM customers WHERE customerNumber = :customernumber");

$stmt->bindParam(':customernumber', $custom_n, PDO::PARAM_INT);

Answer 3

As suggested by Magnus Eriksson, there was a problem with my POST method... even if it seemed to be ok, my variable custom_n had a weird var_dump output string(12)"". I remember that I used the same code previously and was working, so I simply modify the code to be exactly the same: In the page in which I have the form which send the "POST variable", i used this code:

<?php 

            $servername = "localhost";
            $username = "root";
            $password = "";

            $custom_n = array();

            try {
                $conn = new PDO("mysql:host=$servername;dbname=classicmodels", $username, $password);
                $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

                $stmt = $conn -> prepare("SELECT checkNumber, paymentDate, amount, customerNumber FROM payments ORDER BY paymentDate DESC");
                $stmt -> execute();
                $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); 
                foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) { 
                    echo $v;
                    if ($k == 'customerNumber') {
                        array_push($custom_n, $v);
                    }
                }

                $tableCodes = "<table id='buttons'>";
                foreach ($custom_n as $c) {
                    $tableCodes .= "
                        <tr>
                            <td>
                                <form action='customers.php' method='post' target='POPUPW' onsubmit=\"POPUPW = window.open('about:blank','POPUPW', 'width=800px,height=600px');\">
                                    <button type='submit' name='code' value='$c'>Details</button>
                                </form>
                            </td>
                        </tr>
                    ";
                }
                $tableCodes .= "</table>";
                echo $tableCodes;
            }
            catch(PDOException $e) {
                echo "Connection failed: " . $e->getMessage();
            }

            $conn = null;

        ?>

I thought I could have spared some time getting the custom_n from another query that I do to populate a table in that page instead of making another query. I'm not really sure what's wrong with that but with this changing i solved the problem:

<?php 

            $servername = "localhost";
            $username = "root";
            $password = "";

            try {
                $conn = new PDO("mysql:host=$servername;dbname=classicmodels", $username, $password);
                $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

                $stmt = $conn -> prepare("SELECT checkNumber, paymentDate, amount, customerNumber FROM payments ORDER BY paymentDate DESC");
                $stmt -> execute();
                $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); 
                foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) { 
                    echo $v; 
                }

                $customQuery = $conn -> prepare("SELECT customerNumber FROM payments ORDER BY paymentDate DESC");
                $customQuery -> execute();
                $custom = $customQuery->fetchAll(PDO::FETCH_COLUMN);
                $tableCodes = "<table id='buttons'>";
                foreach ($custom as $c) {
                    $tableCodes .= "
                        <tr>
                            <td>
                                <form action='customers.php' method='post' target='POPUPW' onsubmit=\"POPUPW = window.open('about:blank','POPUPW', 'width=800px,height=600px');\">
                                    <button type='submit' name='emp' value='$c'>Details</button>
                                </form>
                            </td>
                        </tr>
                    ";
                }
                $tableCodes .= "</table>";
                echo $tableCodes;
            }
            catch(PDOException $e) {
                echo "Connection failed: " . $e->getMessage();
            }

            $conn = null;

        ?>

I have to thanks everyone, in particular MagnusEriksson, MasivuyeCokile and Pr1nc3 whithout whom I wouldn't ever known about parameterized Prepared Statements. Thank you very much and if you find out why the previous code wasn't working, feel free to comment :D.

Answer 4

This error may thrown because of using MySQL reserved keywords as your table or column names. so make sure not to use reserved keywords in your table or column name.

Here is the MySQL reserved keywords link