locked
asp/mysql select with instr problem RRS feed

  • 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?

    Wednesday, December 3, 2014 4:51 AM

Answers

  • User924634569 posted

    Thanks for your help, this is what works:

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 4, 2014 2:16 AM

All replies

  • 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 '

    Wednesday, December 3, 2014 9:01 AM
  • 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

    Wednesday, December 3, 2014 9:13 AM
  • User924634569 posted

    Thanks for your help, this is what works:

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 4, 2014 2:16 AM
  • 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

    Thursday, December 4, 2014 2:45 AM