PDOException SQLSTATE[42000]: Syntax error or access violation: 1064

Question

I'm trying to take entries via a registration form using php. But I'm getting following error:

exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1'

Here is my code:

 if($_SERVER['REQUEST_METHOD']=='POST'){

$name   = trim($_POST['name']);
$contact= trim($_POST['contact']);
$email  = trim($_POST['email']);

if(!empty($name) && !empty($contact) && !empty($email)){

    try {
        $conn = new PDO("mysql::host='localhost';dbname=majorproject;",'root','');
        //for error reporting and throwing exceptions
        $conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);



        $stmt = $conn->prepare("insert into #_customerdata(customername,customercontact,customeremail) values(:name,:contact,:email)"); 

        $stmt->bindParam(':name',$name,PDO::PARAM_STR);
        $stmt->bindParam(':contact',$contact,PDO::PARAM_INT);
        $stmt->bindParam(':email',$email,PDO::PARAM_STR);

        if($stmt->execute()){
            header('location: ../customersignup.php?status=success');
        }
        else{
            header('location: ../customersignup.php?status=failed');
        }


    } catch (PDOException $e) {
        echo 'CONNECTION NOT ESTABLISHED '.$e;

    }





}

}

I have checked my code but couldn't find any errors.. Any suggestions to solve this problem??

that pound sign `#_customerdata` is the issue. wrap it in ticks `\`` or rename it without one. — Funk Forty Niner, Mar 18 '16 at 19:10
@Fred-ii- : i think your comments should be an answer. I'd add an answer, but you already provided the answer. But kudos to OP... I'd hate to close this question... OP is using PDO and not the deprecated mysql_ functions, and OP is using prepared statement with bind placeholders. Personally, I'd use `bindValue` instead of `bindParam` in this context. [http://stackoverflow.com/questions/1179874/what-is-the-difference-between-bindparam-and-bindvalue](http://stackoverflow.com/questions/1179874/what-is-the-difference-between-bindparam-and-bindvalue) — spencer7593, Mar 18 '16 at 19:34
@spencer7593 It has been done and quoting your comment, *cheers*. — Funk Forty Niner, Mar 18 '16 at 22:20

score 4 · Accepted Answer · edited May 23 '17 at 12:15

As requested.

The # sign in your table name #_customerdata should be escaped using ticks.

insert into `#_customerdata`

Read up on Identifier Qualifiers: http://dev.mysql.com/doc/refman/5.7/en/identifier-qualifiers.html

Assuming that is indeed the table's name and that the # sign doesn't represent a numerical value.

Either that, or rename it without it containing any characters that MySQL would complain about.

If it still complains about it, then you will have no choice but to rename it to just customerdata.

Also as stated in comments by spencer7593:

OP is using PDO and not the deprecated mysql_ functions, and OP is using prepared statement with bind placeholders. Personally, I'd use bindValue instead of bindParam in this context. What is the difference between bindParam and bindValue?

and remove one of the colons here mysql::host.

Consult the manual on connecting with PDO:

http://php.net/manual/en/pdo.connections.php

Example from the manual:

<?php
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
?>

Footnotes:

You should add an exit; after each header, otherwise your code may want to continue to execute.

if($stmt->execute()){
    header('location: ../customersignup.php?status=success');
    exit;
}
else{
    header('location: ../customersignup.php?status=failed');
    exit;
}

Reference:

http://php.net/manual/en/function.header.php

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Then the rest of your code

Sidenote: Displaying errors should only be done in staging, and never production.

+10. MySQL identifiers (e.g. table names, column names) that don't follow the naming rules *must* be properly escaped. Normative pattern in MySQL is to use the backtick character. (If `sql_mode` includes ANSI_QUOTES, then double quotes can also be used to escape identifiers.) It's allowed to use an underscore as the first character of a table_name, without escaping. (For "work", "extract" and "save" tables that aren't part of the regular schema, but in the same database, I start the names of all of those with an underscore. That nicely separates them from the normal schema tables.) — spencer7593, Mar 18 '16 at 22:31
@spencer7593 Thanks for everything. Your comments included. I was going to add a link to MySQL's identifier qualifiers URL, which I'll do now. Edit: Added. — Funk Forty Niner, Mar 18 '16 at 22:37

score 0 · Answer 2 · answered Mar 18 '16 at 19:31

0

If not working yet, try fixing this:

$conn = new PDO("mysql::host='localhost';dbname=majorproject;",'root','');

Between 'mysql' and 'host' you have '::', but it should be ':'

answered Mar 18 '16 at 19:31

rolivares93

11
5

PDOException SQLSTATE[42000]: Syntax error or access violation: 1064

2 Answers2