asp/mysql select with instr problem

Question

User924634569 posted

Hello,
when I use the following code:
rsOpenStr = "select * from inventory where instr(description," &  kwStr &")>0"
rs.Open (rsOpenStr) 
 
   
If I enter a string in the form's text box -- birds for example, I get the following error:
Unknown column 'birds' in 'where clause'.  
If I directly enter 'birds' in the instr function the select statement executes.

Can anyone help please?

Answer 1

User197322208 posted

witgrefe

If I directly enter 'birds' in the instr function the select statement executes.

Could you show how you enter? I think you put '

Answer 2

User753101303 posted

Hi,

Check rsOpenStr. The final string should be:

select * from inventory where instr(description,'birds')>0 but for now more likely you generate :

select * from inventory where instr(description,birds)>0 (missing quotes) so MySQL sees birds as being another column name...

Also usually rather than to assemble yourself a SQL string which causes security and robustness issues, it is strongly suggested to use parameters instead. Try http://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-parameters.html

Answer 3

User924634569 posted

Thanks for your help, this is what works:

"select * from inventory where instr(description,'"  & kwStr & "')>0"

Answer 4

User753101303 posted

But it will fail if someone enter O'Brian or any value with a single quote (one of the reason for which I suggested to always user parameters). Also depending on what is entered in kwStr it would quite easy to change the meaning of your statement: https://en.wikipedia.org/wiki/SQL_injection