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

1
votes

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) ""

4
phpmysqlsqlpdo
You are wide open to SQL injections and should use parameterized Prepared Statements instead of injecting unescaped user data into your queries like that. You've already got the basics set up. In your query, change to: customerNumber = :number and change the execute to: $stmt->execute([':number' => $custom_n]); – M. Eriksson
I'm sorry can you explain better? This is the first time I use php, In other pages I used the same syntax and it's working... can you give me an example of what you mean by parameterized Prepared Statements? – Fabio Magarelli
replace E customerNumber = $custom_n" with '".$custom_n."'" or visit this link stackoverflow.com/questions/46143351/… – Kamran Sohail
Look at the manual for PDO::execute() and you'll see some examples. – M. Eriksson
@SudharshanNair - The only suggestion here should be to use parameterized prepared statements. Anything else is a bad and opens the query up for sql injection attacks. – M. Eriksson

4 Answers

4
votes
$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.

0
votes

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.

0
votes

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);
-2
votes

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