MySQL Prepared Statement fails for SELECT

Databases
1

Was working on updating older mysql statements to prepared sql statements when the following fails.

Try 
  mySQLdb.Connect 
  
  Var _rs As RowSet
  
  '--- Check to see if table exists
  my_sql = "SELECT count(*) " + _
  "FROM information_schema.TABLES " + _
  "WHERE (TABLE_SCHEMA = ?) AND (TABLE_NAME = ?)"
  
  Var ps As MySQLPreparedStatement = MySQLPreparedStatement(mySQLdb.Prepare(my_sql))
  
  ps.Bind(0, ThisSchema)
  ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
  ps.Bind(1, Name_of_Table)
  ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_STRING)
  
Try
  _rs = ps.SelectSQL(my_sql)
Catch error As DatabaseException
  MessageBox "Could not execute. Try again. Message: " + error.Message
  Return
End
 
Catch error As DatabaseException
  MessageBox "Could not connect to db. Try again. Message: " + error.Message
  Return
End

The error occurs at _rs = ps.SelectSQL(my_sql) and states
ErrorNumber: -1
Message: Expected 2 parameters to be bound but received 1
Reason: Expected 2 parameters to be bound but received 1

Tried different formatting but error still occurs. Is the mysql statement or binds not correct? All values are string.

2

I guess you are using Xojo > 2019r2

You need to update your code, see https://documentation.xojo.com/api/databases/database.html#database-selectsql you don’t need MySQLPreparedStatement

3

I don’t know mySQL very well, and maybe it doesn’t make any difference, but I’ve always been in the habit of setting the BindType first, before applying any Bind values. Have you tried something like the following:

ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(0, ThisSchema)
ps.Bind(1, Name_of_Table)
4

You are giving your sql to the prepared statement, and that is becoming the first, and only, parameter. The sql is already baked into the prepared statement.

1 Like
5

Thanks

After looking at XOJO Docs I read this

The use of the prepared statement classes is rare because Database.SelectSQL and Database.SQLExecute utilize them automatically. See PreparedSQLStatement for information on cases where using prepared statement classes is appropriate.

I was going on readings from several years ago where the docs informed you to move to these statements to improve speed and security. So it appears Prepared Statements for SQLExecute and SQLSelect incorporate Prepared Statements.

Is that the understanding I should to take away from this?

6

If I am not crazy enough, when using manual binding, you should just call ps.SQLSelect(), you already passed the query to the Prepare(query) and binded values using ps.Bind(). When using “auto binding”, you should use ps.SelectSQL(Paramarray bindValues() as Variant)

So I believe you need to change the line:

to
_rs = ps.SQLSelect()

The docs need enhancements to clarify such kind of thing.

7

Isn’t better to just move to the simpler binding model like:

Try 
  
  mySQLdb.Connect 
  
  Var _rs As RowSet
  
  '--- Check to see if table exists
  my_sql = "SELECT count(*) " + _
  "FROM information_schema.TABLES " + _
  "WHERE (TABLE_SCHEMA = ?) AND (TABLE_NAME = ?)"
  
  Try
    
    _rs = mySQLdb.SelectSQL(my_sql, thisSchema, Name_of_Table)
    
  Catch error As DatabaseException
    
    MessageBox "Could not execute. Try again. Message: " + error.Message
    Return
    
  End
  
Catch error As DatabaseException
  
  MessageBox "Could not connect to db. Try again. Message: " + error.Message
  Return
  
End
8

Yes - that is it - thank you

1 Like
9

Yes. Originally my app didn’t use prepared statements at all. Then I decided perhaps it should, to avoid SQL injection, but I wasn’t going to add hundreds of lines of code to achieve that. So I made wrapper methods which did the binding for strings (the main problem for injection attacks), and then later Xojo had added new methods which incorporated all that and not just for strings. So I changed to use those and was able to discard my wrapper methods.

IMO, one should always read the Release Notes for each new relase to see if there’s anything there that is useful and which helps simplify one’s app, make it more secure, faster, easier to maintain, etc.

Edit: I think the built-in binding was one of the most useful additions Xojo has made.

10

I agree with your ‘Edit:’

For me I have extensive db, GraffitiSuite, and MonkeyBread integrations in a large WE app. This covers a significant number of SQL SELECT & EXECUTE statements, GraffitiToolbar, GraffitiGrid, GraffitiMenu, GraffitiMenuItem, GraffitiPDF, GraffitiUploader, and MBS Excel capabilities. After resolving a number of changes to enable full functionality in the new framework the next piece was to take a look at security and speed improvements along the lines of what I read several years ago which is how I got to this thread.

After moving to the latest XOJO 2021 r3.1 there has been a significant speed improvement. Perhaps part of resolving full functionality and part what you mentioned in your ‘Edit:’

Thanks to everyone for replying. I am closing in on final updates. We hope everyone has a happy, safe and prosperous New Year.

11

I have noticed that when your sql statement is wrong (like you mispelled a column) it gives that error “expected 0 parameters to be bound but received 1” on the database exception error instead of saying that you have a mistake in your sql. But I don’t know why ?

Jean-Maurice

12

You’re not the only one who noticed, but it looks like Xojo pushed away all the people who would report it thoroughly. Couldn’t find any tickets about the issue.

1 Like