Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]

Question

I am migrating from normal SQL to PDO because I let a friend of mine test if I have any weak points and he adviced me to PDO because he found a lot of weak points.

So here is my full error:

Fatal error: Uncaught 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 ' in /home/ubuntu/workspace/post.php on line 54

( ! ) PDOException: 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 '? (id, title, info_bys, info_shorts, info_longs, email, ' at line 1 in /home/ubuntu/workspace/post.php on line 54

And here is my code:

    $stmt = $db->prepare("INSERT INTO  :portal
    (`id`, `title`, `info_bys`, `info_shorts`, `info_longs`, `email`, `filename`, `filepath`, `filename2`, `filepath2`, `approved`) 
    VALUES ('', ':title', ':by_information', ':short', ':long_information', ':email', ':filename', ':filetarget', ':filename2', ':filetarget2',  'false'");
    $stmt->execute(array(':portal' => $portal, ':title' => $title, ':by_information' => $by_information, ':short' => $short, ':long_information' => $long_information, ':email' => $email, ':filename' => $fileName, ':filetarget' => $fileTarget, ':filename2' => $fileName2, ':filetarget2' => $fileTarget ));
    echo $affected_rows.' were affected';

Is there something I cant use in PDO that I can use in SQL or am I just typing the wrong stuff.

Hope someone can help.

EDIT:

New code:

    function buildQuery( $get_var ) 
{
    switch($get_var)
    {
        case 1:
        $portal = $_POST['portal'];
            break;
    }

                $stmt = $db->prepare("INSERT INTO  :portal
            (`id`, `title`, `info_bys`, `info_shorts`, `info_longs`, `email`, `filename`, `filepath`, `filename2`, `filepath2`, `approved`) 
            VALUES (:title, :by_information, :short, :long_information, :email, :filename, :filetarget, :filename2, :filetarget2,  'false'");
            $stmt->execute(array(':portal' => $portal, ':title' => $title, ':by_information' => $by_information, ':short' => $short, ':long_information' => $long_information, ':email' => $email, ':filename' => $fileName, ':filetarget' => $fileTarget, ':filename2' => $fileName2, ':filetarget2' => $fileTarget ));
            echo $affected_rows.' were affected';
}

Table names can't be bound. You can make a whitelist can compare the name against that then just pass it in. — chris85, Mar 23 '16 at 18:55

Xorifelse · Answer 1 · 2016-03-23T19:11:05.503

2

Three issues with the code:

As stated by crhis85 (upvote), you can't bind table names.
PDO prepare takes care of the escaping and quotes.

VALUES ('', ':title', ':by_information', ':short', ':long_information', ':email', ':filename', ':filetarget', ':filename2', ':filetarget2', 'false'");

The issue here is that if you define a param to a string (PDO::PARAM_STR) the values are double quoted with single quotes. Instead do this:

`VALUES ('', :title, :by_information, :short, ....");`

Don't insert an ID, this should be set on auto increment and is done automatically.

'INSERT INTO table (title, ...'

Also, backticks (``) are used to let the database driver know that you're using this value and is not to be used as a reserved keyword. In other words, entirely obsolete in this query.

edited Mar 23 '16 at 19:11

answered Mar 23 '16 at 19:01

Xorifelse

7,878
1
27
38

It doesnt return any errors but it doesnt give me the text that is set, This : echo $affected_rows.' were affected'; – Rik Nijdeken Mar 23 '16 at 19:04
@RikNijdeken update the question to your new code. – chris85 Mar 23 '16 at 19:08
@RikNijdeken `:portal` still needs to be `$portal`, and that should be checked before being passed in. `id` shouldn't be passed in, and you should give `approved` a default value of `false`. – chris85 Mar 23 '16 at 19:12
@RikNijdeken `"INSERT INTO $portal (\`title\`, ... ";` – Xorifelse Mar 23 '16 at 19:13
@chris85 I edited it but it still doesnt work. – Rik Nijdeken Mar 23 '16 at 19:13
try: `echo $stmt->rowCount() . ' rows were affected.';`. – Jeroen Bellemans Mar 23 '16 at 19:14
@Xorifelse Still doesnt give me an echo with "Bla Bla" were affected – Rik Nijdeken Mar 23 '16 at 19:15
1

@RikNijdeken where is `$affected_rows` defined? What is `$_POST['portal']`? – chris85 Mar 23 '16 at 19:15
@chris85 Ow what wait, Thanks for noticing :P I think its old code. And $_POST['portal'] is a posted select value – Rik Nijdeken Mar 23 '16 at 19:16
@RikNijdeken I know that. If it is a reserved term though this will also fail because it isn't in backticks. – chris85 Mar 23 '16 at 19:21
@chris85 How do i backtick it then? – Rik Nijdeken Mar 23 '16 at 19:21
Something like `"\`" . $_POST['portal'] . "\`"` **BUT** check that value first. Otherwise preparing is almost useless because you are just passing in user provided data to query. – chris85 Mar 23 '16 at 19:22
`"\`{$_POST['portal']}\`"` would work too, but you should whitelist it. – Xorifelse Mar 23 '16 at 19:24
@chris85 I am checking every input field earlier in the code. I'll check it out. – Rik Nijdeken Mar 23 '16 at 19:24
Giving me another error: Parse error: syntax error, unexpected '`' in /home/ubuntu/workspace/post.php on line 61 Already fixed it i used ' instead of " But it still didnt work – Rik Nijdeken Mar 23 '16 at 19:26
@RikNijdeken didnt insert...or? update question.. – chris85 Mar 23 '16 at 19:33
@chris85 It didnt insert the data that I put in the form. – Rik Nijdeken Mar 23 '16 at 19:34
Let us continue this discussion in [chat](http://chat.stackoverflow.com/rooms/107174/xorifelses-room) – Xorifelse Mar 23 '16 at 19:37

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]

1 Answers1