# Newbie Prepared Statement Difficulty **URL:** \ **Category:** Databases\ **Created:** [December 21, 2016, 1:39am UTC](https://forum.xojo.com/t/newbie-prepared-statement-difficulty/35085 "2016-12-21T01:39:17Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Mike\_Pearson](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/d6d6ee/32.png) [@Mike\_Pearson](https://forum.xojo.com/u/Mike_Pearson)\ **Post date:** [December 21, 2016, 1:39am UTC](https://forum.xojo.com/t/newbie-prepared-statement-difficulty/35085/1 "2016-12-21T01:39:17Z")
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! ---
**Author:** ![Mike\_Pearson](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/d6d6ee/32.png) [@Mike\_Pearson](https://forum.xojo.com/u/Mike_Pearson)\ **Post date:** [December 21, 2016, 2:27am UTC](https://forum.xojo.com/t/newbie-prepared-statement-difficulty/35085/2 "2016-12-21T02:27:18Z")
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… ---
**Author:** ![Norman\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/858c86/32.png) [@Norman\_P](https://forum.xojo.com/u/Norman_P)\ **Post date:** [December 21, 2016, 2:30am UTC](https://forum.xojo.com/t/newbie-prepared-statement-difficulty/35085/3 "2016-12-21T02:30:52Z")
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/sql-syntax-prepared-statements.html) [http://dev.mysql.com/doc/refman/5.7/en/prepare.html](http://dev.mysql.com/doc/refman/5.7/en/prepare.html) ---
**Author:** ![Mike\_Pearson](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/d6d6ee/32.png) [@Mike\_Pearson](https://forum.xojo.com/u/Mike_Pearson)\ **Post date:** [December 21, 2016, 2:46am UTC](https://forum.xojo.com/t/newbie-prepared-statement-difficulty/35085/4 "2016-12-21T02:46:55Z")
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` ---
**Author:** ![Norman\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/858c86/32.png) [@Norman\_P](https://forum.xojo.com/u/Norman_P)\ **Post date:** [December 21, 2016, 3:01am UTC](https://forum.xojo.com/t/newbie-prepared-statement-difficulty/35085/5 "2016-12-21T03:01:28Z")
They basically make [SQL injection attacks](https://en.wikipedia.org/wiki/SQL_injection) 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](http://www.bkeeney.com/rbinto/activerecord/) which is already done for you ---
**Author:** ![Mike\_Pearson](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/d6d6ee/32.png) [@Mike\_Pearson](https://forum.xojo.com/u/Mike_Pearson)\ **Post date:** [December 21, 2016, 5:59am UTC](https://forum.xojo.com/t/newbie-prepared-statement-difficulty/35085/6 "2016-12-21T05:59:45Z")
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. ---
**Author:** ![scott\_boss](https://forum.xojo.com/letter_avatar_proxy/v4/letter/s/c5a1d2/32.png) [@scott\_boss](https://forum.xojo.com/u/scott_boss)\ **Post date:** [December 21, 2016, 2:09pm UTC](https://forum.xojo.com/t/newbie-prepared-statement-difficulty/35085/7 "2016-12-21T14:09:56Z")
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. ---
**Author:** ![system](https://forum.xojo.com/uploads/default/original/1X/455b4dcc0e61630e9f81940004ddffa49c432a46.png) [@system](https://forum.xojo.com/u/system)\ **Post date:** [October 29, 2020, 6:35pm UTC](https://forum.xojo.com/t/newbie-prepared-statement-difficulty/35085/8 "2020-10-29T18:35:19Z")