1

When I query in my database microsoft access, with this code..

dc.rs = dc.st.executeQuery("select count(*) from Accounts where username like '%"+searchTF.getText()+"%' OR firstname like '%"+searchTF.getText()+"%'");

I got this result, not sure if this is error because it is written in black text.

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

I want to search by username or by firstname that's why i put an OR there.

Do anyone know where I got some problem?

Kermit
  • 33,827
  • 13
  • 85
  • 121
Katherine
  • 573
  • 4
  • 17
  • 43
  • 1
    `searchTF.setText(";DELETE * FROM Accounts;");` – Perception Nov 28 '12 at 08:06
  • 4
    Your code is vulnerable to [SQL injection attacks](https://en.wikipedia.org/wiki/SQL_injection)! You should **never** build SQL statements from user input like that. Use a prepared statement with parameters instead! – Joachim Sauer Nov 28 '12 at 08:06
  • 2
    @Perception: `"'; DELETE * FROM Accounts; --"` probably works "better". – Joachim Sauer Nov 28 '12 at 08:07
  • @JoachimSauer - first part of the query doesn't actually have to run successfully. – Perception Nov 28 '12 at 08:07
  • @Perception: yes, but with your text the `DELETE` is still part of the searched-for string (as you don't close the `'`). – Joachim Sauer Nov 28 '12 at 08:09
  • Ah yes, thats true. Good catch. – Perception Nov 28 '12 at 08:10
  • 2
    [Exploits Of A Mom](http://xkcd.com/327/) – Filburt Nov 28 '12 at 08:15
  • I guess you should post more code, about how your rs and statements are instantiated. From the message it seems that your query has 1 parameter declared, when it uses none (which is very bad practice, as pointed above) – jbl Nov 28 '12 at 08:20
  • what's the query string that you are passing to executeQuery? can you print it before executing it? i guess there's some problem there... – fthiella Nov 28 '12 at 08:22
  • @Perception I get my text from the text field, I didn't understand why I should use delete from. – Katherine Nov 28 '12 at 08:32
  • @fthiella i write the string directly in the executeQuery... I'll try to print – Katherine Nov 28 '12 at 08:33
  • @Katherine: you shouldn't use DELETE. Read the Wikipedia article I linked to. With your current code the **end user** can run arbitrary SQL code through your search field, if he knows how to format it. – Joachim Sauer Nov 28 '12 at 08:44
  • @JoachimSauer I understand what you mean now. Thanks for the info – Katherine Nov 28 '12 at 08:53

1 Answers1

2

Microsoft Access uses * for the wildcard

MS Access also uses single quotes according to this page

http://refactoringself.com/2011/06/22/ms-access-error-too-few-parameters-expected-x/

using a prepared Statement

String queryString = "select count(*) from Accounts where username like ? OR firstname like ?";
PreparedStatement  stmt= con.prepareStatement(queryString );
stmt.setString(1, "*" + searchTF.getText() + "*");
stmt.setString(2, "*" + searchTF.getText() + "*");
stmt.executeQuery();

or the less secure way

dc.rs = dc.st.executeQuery("select count(*) from Accounts where username like " + 
    "\'*" + searchTF.getText() + "\'* OR firstname like \'*" + searchTF.getText() + "*\'");
Rebzie
  • 310
  • 1
  • 3
  • 9
  • You need to add the stars to the strings in the prepared statement as well. Also, it's probably a good idea to point out the *good* approach **first** ;-) – Joachim Sauer Nov 28 '12 at 08:42
  • the * didn't work... i tried the prepared statement. and it says "Too few parameters. Expected 3." – Katherine Nov 28 '12 at 08:57
  • 1
    @Katherine have you double-checked that the table name and column names are correct ? – jbl Nov 28 '12 at 13:29
  • @jbl OMG... a million thanks for reminding me.. :) .... i guess i'm just too tired that i forgot to check the column names .. you are right, the column name firstname should be first_name – Katherine Nov 28 '12 at 16:01
  • @Katherine you're welcome :-) But don't forget the prepared statement part. It's the way to go :-) – jbl Nov 28 '12 at 16:19