Android Question Query cmd.parameter RJDC2

[luisro]

hi, my question is this:


My query in the file

sql.select= SELECT * FROM table where name = 1 order by ? desc


in my app is this:

parametro = "Example"

Sub Operation
Dim cmd As DBCommand
cmd.Initialize
cmd.Name = "select"
cmd.Parameters = Array As Object(parametro)
reqManager.ExecuteQuery(cmd, 0, "select")
End Sub

in particular my question is you can pass a parameter to the query to sort the same. (Order by? Desc) which I can then change to sort by X my sql query.

Ty.

 

[OliverA]

Have you tried it? I'm gonna guess you're going to get an error message from the DB, but I could be wrong. A work around may be something based on this (https://www.b4x.com/android/forum/threads/variable-table-name-through-jrdc.83262/), but I would not recommend it unless you can really guarantee a way to escape the column in such a way as to prevent SQL injection attacks.

 

[luisro]

run the query without problem but wrong, does not filter the results by the parameter, sensibly what I want to save the create 15 queries in the properties file, I have a spinner with the names of 3 parameters (columns) and simply what I mean is take the Spinner1.SelectedIndex and pass it by parameter and filter the query by (date, price, etc).

 

[OliverA]

run the query without problem but wrong

Are you using the build in Android database (based on SQLite)? What version of Android?

Update: Nevermind! What database are you using on the server?

 

[luisro]

WampServer 2.2 DB PhpMyadmin

parametro = "lote"

Sub Operation
Dim cmd As DBCommand
cmd.Initialize
cmd.Name = "select_chicha"
cmd.Parameters = Array As Object(parametro)
reqManager.ExecuteQuery(cmd, 0, "select")
End Sub

Real query.

sql.select_chicha=SELECT `imagen`, `lote`, `nombre`, `gramaje`, `precio`, `leche`, `azucar`,`instantaneo`, `categoria`, DATE_FORMAT((fecha),'%d/%m/%Y') as 'fecha' FROM `ventaskiana` where categoria = "CHICHA" AND nombre != "CHICHA KIANA" order by ? desc


I have perfect connection with db and all other queries, the only thing is that the filtering does not take it correctly.

ACT: Directly from the db, I make the query without any problem changing (?) by (lote), now I do not know if it distorts the text and therefore does not take it.

 

[OliverA]

When you use parameters in the order by, it puts that parameter in as a value parameter and your SQL statement goes from:

SELECT `imagen`, `lote`, `nombre`, `gramaje`, `precio`, `leche`, `azucar`,`instantaneo`, `categoria`, DATE_FORMAT((fecha),'%d/%m/%Y') as 'fecha' FROM `ventaskiana` where categoria = "CHICHA" AND nombre != "CHICHA KIANA" order by ? desc

to

SELECT `imagen`, `lote`, `nombre`, `gramaje`, `precio`, `leche`, `azucar`,`instantaneo`, `categoria`, DATE_FORMAT((fecha),'%d/%m/%Y') as 'fecha' FROM `ventaskiana` where categoria = "CHICHA" AND nombre != "CHICHA KIANA" order by 'lote' desc

Notice the single quotes instead of the back tick that are used for your column names. Order by a value is actually acceptable SQL. What happens here is that if you are actually sorting items by the value 'lote' first and then all other values are listed in the usual unsorted order. Normally you would tell SQL what column that applies to and since there is none here I do not know what MySQL selects as the column to use (first one?). So yes, you're passing (for MySQL) valid SQL, it is just not doing what you are expecting (see https://stackoverflow.com/questions/6332043/sql-order-by-multiple-values-in-specific-order).

That is just how SQL works and how parameterized SQL works. If you wan't to work around the limitations, then you need to look at the link I provided earlier. Once more that solution needs to be approached with caution, since it can open you up for SQL injection attacks. You may mitigate that issue by seeing if the column exists in the catalog of your database or creating a table of valid column names allowed for such substitution. You would then query for that column and use the returned value of the query as input to the dynamic query instead of just blindly plugging in what you received over the network. This is just a suggestion.

 

[luisro]

I have a column called batch that is of integer type, and if it orders by "batch", only that when assigning it by parameter from the application executes the query, but does not sort by batch does not generate me no error, but hey, for reasons of time solvent creating direct queries from the file and calling from the app, are a little more line of code but less time. Thank you very much for your help and your time.

 

[OliverA]

I have a column called batch that is of integer type, and if it orders by "batch", only that when assigning it by parameter from the application executes the query, but does not sort by batch does not generate me no error

Just to restate, the parameters that you pass to a query's ORDER BY statement in a parameterized query are values, not column names (that just how it is, that is how parameterized queries work. It is not a bug, it is a feature). So, in your case ORDER BY sees the value "batch", not the column "batch". For MySQL, you can use ORDER BY to order by a specific value and therefore you are not going to get an error message, since no syntactical error has been committed (MySQL sees a valid query).