SQL Command to search from Java program

Question

I am attempting to allow keyboard input from my Java Project to search for a Car License number (VARCHAR), from my database. I am getting an error in my tester class about SQL syntax error. What would be the correct procedure so that when I search for a license it will display that license. Thanks in advance

public Car getCar(String searchLicense) {
    Car foundCar = new Car();
    try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(url + dbName, userName, password);
        statement = conn.createStatement();
        resultSet = statement.executeQuery(
                "select * from eflow.registration.cLicense where="+searchLicense);

        while (resultSet.next()) {
            foundCar = new Car(resultSet.getInt("cID"), resultSet.getString("cLicense"),
                    resultSet.getInt("cJourneys"), resultSet.getString("cUsername"),
                    resultSet.getString("cPassword").toString());
        }
        conn.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return foundCar;
}

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.cLicense where=' at line 1 — user3079838
– user3079838, Commented Mar 1, 2017 at 16:22
A brief look at the code ... cLicense is a field right? You select from a TABLE not a field. Your where clause is also incorrect. Change it to " where cLicense = 'VALUE' — Dakoda
– Dakoda, Commented Mar 1, 2017 at 16:24
without knowing the schema ... try "select * from eflow.registration where cLicense = '" + searchLicense + "';" — Dakoda
– Dakoda, Commented Mar 1, 2017 at 16:28

Keval Pithva · Accepted Answer · 2017-03-01 17:20:49Z

1

You missing single quote and column name also..

resultSet = statement.executeQuery(
                    "select * from eflow.registration.cLicense where cLicenseName='"+searchLicense+"'");

Better solution,try this..

  resultSet = statement.executeQuery(
                "select * from eflow.registration.cLicense where cLicenseName like '%"+searchLicense+"%'");

edited Mar 1, 2017 at 17:20

answered Mar 1, 2017 at 17:15

Keval Pithva

6202 gold badges6 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Youcef LAIDANI Over a year ago

he select from db.table.column eflow.registration.cLicense did you check that, this is a second error you solved :)

Keval Pithva Over a year ago

But he did not mention any column name as well as table name specifically.

Nir Levy · Accepted Answer · 2017-03-01 16:40:38Z

0

The direct problem you're talking about it that you are missing quotation on your query, since it is a string. so what @Dakoda suggested in the comments should solve it.

however, the bigger issue here is that you are vulnerable to SQL injection, as you are allowing user input into your query. If I'll put input like xxx' or 'a' ='a I'll be able to fetch your entire database.

You should use parameterized query to protect yourself

answered Mar 1, 2017 at 16:40

Nir Levy

13k3 gold badges23 silver badges41 bronze badges

1 Comment

user3079838 Over a year ago

Here is the message I get after altering the code You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Collectives™ on Stack Overflow

SQL Command to search from Java program

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related