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:
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?
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.
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.
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.
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 ?