PDO syntax error for SELECT query

Question

I have a basic query which gets all fields and exports the file but it keeps giving me an error. My code looks like this:

$array = ['users'];

foreach($array AS $i){
        $file = $i.'.sql';

        $stmt = $pdo->prepare("SELECT * FROM ? INTO OUTFILE ?");
        try {
            $stmt->execute(array($i,$file));
        } catch (PDOException $e) {
            $log .= $e -> getMessage().'........ \n ';
        }
}

I keep getting this error how ever:

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 ''users' INTO OUTFILE 'users.sql'' at line 1.

What is the correct syntax for this ?

Try SELECT * INTO 'users.sql' FROM `users` http://dev.mysql.com/doc/refman/5.6/en/select-into.html — Nouphal.M, Dec 22 '13 at 05:50

score 2 · Accepted Answer · edited May 23 '17 at 12:28

2

It(Syntax) should be the other way around

SELECT * INTO OUTFILE ? FROM ?;

See the documentation here

EDIT :

As JAL clarified, The table name cannot be passed as a parameter under a PreparedStatement. So your query should be like

SELECT * INTO OUTFILE ? FROM users;

edited May 23 '17 at 12:28

Community

1
1

answered Dec 22 '13 at 05:56

Shankar Narayana Damodaran

68,075
43
96
126

Hmm that did seem to fix that issue but i got `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 ''users'' at line 1` for some reason it does not like the table name – Sir Dec 22 '13 at 05:57
@Dave, See JAL's answer , that is the other part of this answer. – Shankar Narayana Damodaran Dec 22 '13 at 06:15

score 1 · Answer 2 · edited May 23 '17 at 12:11

1

You cannot prepare a statement where the table name is a parameter.

See Can PHP PDO Statements accept the table or column name as parameter?

edited May 23 '17 at 12:11

Community

1
1

answered Dec 22 '13 at 06:03

JAL

21,295
1
48
66

@Dave I recall running into this problem myself working with PDO long ago. It's nice to find an explanation. – JAL Dec 22 '13 at 06:43

PDO syntax error for SELECT query

2 Answers2