MySQL syntax error in simple prepared statement?

Question

I've been using prepared statements over the past two weeks and had no problems. Today, I'm completely baffled as to what I'm doing wrong here. I've been doing somewhat complex queries using the same technique that I am using here.

This is the query I want to perform:

'SELECT username FROM ? WHERE password=?'

After this didn't work I tried boiling it down and trying the simplest version I can use to build up from. Yet that still resulted in a syntax error.

My Current Code:

...

// mysqli object created and connection established
if (!($stmt = $mysqli->prepare('Select * FROM ?')))
{
  // Prepared statement for retrieving a user failed
  echo 'Prepare failed: (' . $mysqli->errno . ') ' . $mysqli->error;
  exit;
}

// Prepared statement for inserting a new user created
if (!($stmt->bind_param('s', $table)))
{
  // Paramater binding failed
  echo 'Binding parameters failed: (' . $stmt->errno . ') ' . $stmt->error;
  exit;
}

...

The Error:

Prepare failed: (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

This is for a school project so if you would like you can give suggestions to make this more secure. I'm using phpass to hash my passwords.

Simple: You can't use ? for table names. Choose an actual table name or use a variable for it. — Funk Forty Niner
– Funk Forty Niner, Commented May 30, 2014 at 15:47

Your Common Sense · Accepted Answer · 2014-05-30 15:51:09Z

2

Make it

SELECT username FROM users WHERE password=?

as there should be only one users table to choose passwords from. If you think opposite, then you have to start your class over.

answered May 30, 2014 at 15:51

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andy Lester · Accepted Answer · 2014-05-30 15:47:40Z

2

You cannot bind table names to variables. You can only bind values that will be replaced at query time.

answered May 30, 2014 at 15:47

Andy Lester

94.2k16 gold badges106 silver badges162 bronze badges

4 Comments

Funk Forty Niner Over a year ago

I should have made my comment, an answer (but chose not to). ;-) given it was almost a minute before this.

Jeffpowrs Over a year ago

Interesting. Why is that? It seems totally legitimate to allow that. I'm assuming it's for security purposes?

Your Common Sense Over a year ago

@JeffPowers it's because of the dual purpose. Prepared statement is used to prepare the query execution on the server side. but without field name its impossible.

Andy Lester Over a year ago

The optimizer needs to create a plan for the statement. It can't create a plan if it doesn't know what table and columns it will be operating on.

Mike Brant · Accepted Answer · 2014-05-30 15:57:18Z

As others have pointed out, you can't parametrize database objects - databases, tables, column names, etc. - in a prepared statement. It might help for me to explain at a high level what a prepared statement does, and the reason for this will be clear.

When a prepared statement is "prepared" the DB actually formulates the query execution plan for the query, knowing that later some of the actual data will be passed in as parameters. This is what makes prepared statements create as protection against SQL injection, as there is then no way, just by passing "unsafe" data that the query be be made to do something other than what was planned at preparation time.

There is no way for this query execution planning to take place if you don't know the names of tables, columns, etc. that will be acted upon in the query.

Collectives™ on Stack Overflow

MySQL syntax error in simple prepared statement?

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related