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.
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.
It is possible that you need to add some parentheses, thus:
... WHERE (harga BETWEEN 0 AND 200000) AND (tgl BETWEEN ?2 AND ?3)
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.
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.
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?