Help with MySQL Query

Hi,

I want to query price between 0 and 200 on a specific date.

sql = "SELECT * FROM " + ComboBox1.Text + Label1.Text.Trim + " WHERE harga BETWEEN 0 AND 200000 AND tgl BETWEEN '" + CustomerSearchFor.Text + "' AND '" + CustomerSearchFor1.Text + "'"

The tgl is on date format
customersearchfor and custoemrsearchfor1 is a textfield with Sql date format yyyy-mm-dd

When I do test it, the result was not filtering properly.

any helps

thanks
arief

can you put a System.debuglog of your sql variable?

Customer can search for ‘Banana’
Or paste total rubbish into these fields.
Worse, they could add SQL commands and wreck your database.

You should change to using parameterised queries, which will avoid this ‘SQL injection’ problem.

==========

What does sql actually contain at the point when you execute the SQL?
(ComboBox1.Text + Label1.Text.Trim could contain anything)

1 Like

Take a look at the documentation:

https://documentation.xojo.com/api/databases/sqlitepreparedstatement.html

If you do the query directly, does it work?

Do you have different tables with names equal the contents of ComboBox1.Text + Label1.Text.Trim

When you say “the result was not filtering properly” can you give some examples of data in the database, query values and result?

  1. That DOES need to be changed to use a prepared statement form such as:
sql = "SELECT * FROM ?1 WHERE harga BETWEEN 0 AND 200000 AND tgl BETWEEN ?2 AND ?3"
db.SelectSQL (sql, (ComboBox1.Text + Label1.Text.Trim), CustomerSearchFor.Text, CustomerSearchFor1.Text))

.
If you do NOT make this change, your database is vulnerable to being damaged by the customer.

  1. It is possible that you need to add some parentheses, thus:
... WHERE (harga BETWEEN 0 AND 200000) AND (tgl BETWEEN ?2 AND ?3)

.
It makes it more readable, anyway.

The table name can be an argument (?1) ?
I think I remember reading that can’t be but I’m not sure.

Ah, that needs checking.

You cannot parasitise a table name in a prepared statement.

Normal SQL statements are processed by building an execution plan and then running it.

When you create a prepared statement it produces the execution plan at that time. You can then it run as many times as you like with differing data. If you are running the statement multiple times it is vastly faster than executing a new statement over and over again.

Without table names it is impossible for that execution plan to be created, so the process fails.

1 Like

the textfield was formatted into SQL Date format.
I need this to run on local database and for my own purpose.

Will tried to convert it into PreparedSQL Statement.

Thanks
Arief

Yes, something like this.
will do some test.

thanks
arief

Yes, the table names stored in a combo box +text on label.

thanks
arief

Then consider my suggestion suitably modified.

In my case, there are no instances where re-running a prepared statement would save time, they are generally only run once and the connection closed. I use the API2 database methods for security only.

BTW, I assume you meant “parameterise” :grin:

Wow, yes indeed. That’s autocomplicate for you (yes, I did this one deliberately) :grin:

1 Like

I have convert it into preparedsqlstsement but still has an error.

dim ps1 as MySQLPreparedStatement

ps1 =  mDB.Prepare("SELECT * FROM arion_report WHERE harga BETWEEN 0 AND 200000 AND tgl BETWEEN ?1 AND ?2")

ps1.BindType(1,MySQLPreparedStatement.MYSQL_TYPE_DATE)
ps1.BindType(2,MySQLPreparedStatement.MYSQL_TYPE_DATE)

ps1.Bind(1,CustomerSearchFor.text)
ps1.Bind(2,CustomerSearchFor1.text)

ps1.SQLExecute

so there is no chance to use custom table name based from combobox right?

thanks
arief

Sorry to say that prepared statements had nothing to do with your problem just a recommendation to avoid other possible problems.

Also what version of Xojo are you using? It looks like you are using deprecated code (SQLExecute for example)

Now we can focus again in your problem, can you give more information like:

  • the query works directly on the database?
  • what are the values used for yyyy-mm-dd?
  • are you sure the same values are used with your code? (as said also, can you output sql to make sure is the query you want?)
  • can you share a few records (or examples) of what you have, what you query, what you expect and what you get?

You saying “the result was not filtering properly” does not help to narrow the problem.

There is no need to do it in that way. Just look at what I posted and do it that way. Two lines of code instead of six.