Newbie Prepared Statement Difficulty

Hi,
Having a bit of trouble with using prepared statements.

The following works fine (from within a database class I’ve created):

Dim sql As MySQLPreparedStatement = Self.Prepare("SELECT * FROM suppliers") Return sql.SQLSelect

But the following does not:

Dim sql As MySQLPreparedStatement = Self.Prepare("SELECT * FROM ?") sql.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING) sql.Bind(0, "suppliers") Return sql.SQLSelect

Can someone kindly explain to me what’s going wrong?
Much appreciated!

Also, this works:

Dim sql As MySQLPreparedStatement = Self.Prepare("SELECT * FROM suppliers WHERE Name = ?") sql.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING) sql.Bind(0, "Amazon") Return sql.SQLSelect

But again, this does not:

Dim sql As MySQLPreparedStatement = Self.Prepare("SELECT * FROM ?") sql.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING) sql.Bind(0, "suppliers") Return sql.SQLSelect

Are there only certain parts of an SQL statements into which substitutions can be made?
e.g. you can make substitutions after a “WHERE” statement, but not after a “FROM” statement?

Is this accurate?
If so, can someone kindly link me to documentation describing what can and cannot be substituted in a prepared statement?
Thanks in advance…

The name of the table cannot be a parameter like this

You can only use them for

  • values in a where clause (ie/ select * from suppliers where supplierID = ? )
  • values in the VALUES clause of an INSERT statement (ie/ insert into suppliers (supplierID) values ( ? ) )

http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html
http://dev.mysql.com/doc/refman/5.7/en/prepare.html

Thanks Norman.

Ok, so if I DO want to substitute a table name (I’m trying to write generic get/set methods) I can do the following:

Dim stmt As String = "SELECT * FROM %table%" stmt = stmt.Replace("%table%", "suppliers") Dim sql As MySQLPreparedStatement = Self.Prepare(stmt) Return sql.SQLSelect

…but then the question is, why use the prepared-statement bindings?

Isn’t:

Dim stmt As String = "SELECT * FROM suppliers WHERE Name = %name%" stmt = stmt.Replace("%name%", "'Safeway'") Dim sql As MySQLPreparedStatement = Self.Prepare(stmt) Return sql.SQLSelect

Pretty much the same as:

Dim sql As MySQLPreparedStatement = Self.Prepare("SELECT * FROM suppliers WHERE Name = ?") sql.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING) sql.Bind(0, "Safeway") Return sql.SQLSelect

They basically make SQL injection attacks pretty darned close to impossible
ESP if user in put is part of the data etc

If your looking for “generic ways to read / write data” you might look at something like BKeeney’s ActiveRecord which is already done for you

Whoa!! Thanks for the link to BKeeney’s ActiveRecord.
It’s…overwhelming.

I had no idea implementing a generic database handler would be so complicated! It’s certainly robust, I’ll say that for sure!
I’ll have to go through it and see if I can simplify it to my needs…

Thanks again.
M.

I have started using ActiveRecord, and using the ORM (Object Relationship Manager?) it has completely changed my life when it comes to databases. I just create an object (that maps back to the table I want to add a row into) and just manipulate the object like I would any other object, then when I tell it to save, it saves it as a record in the database table. For me, it doing all the heavy lifting when it comes to the database work (SQL, CRUD, etc), it makes my life much easier. Plus I dont have to become a Database Godess (like he has on staff).

When @Bob Keeney first released ActiveRecord, I was very impressed with it. And over the years he (as his team) has greatly improved it. Making me even more impressed.