SQL query with user input

Question

I have an error

java.sql.SQLException: ORA-00933: SQL Command not properly ended

when I try to execute the following SQL Statement.

In this statement, it is supposed to display all the rental details and feedback for a particular customer when a customer name is searched. "query" refers to the user input when the user searches for a name (CustomerName) in the database.

 SELECT c.CustomerName, r.Rental_ID, r.Staff_ID, r.RentalDate, r.DueDate,
 r.Customer_ID, f.Description 
 FROM Rental r, Customer c, FeedBack f 
 WHERE f.Customer_ID = r.Customer_ID 
 AND c.Customer_ID = f.Customer_ID 
 ORDER BY DueDate 
 WHERE CustomerName like "+ query +";

What went wrong?

i hope you are not literally appending the user provided name into your query and are instead using PreparedStatements so as to avoid the glaring sql injection vulnerability. — jtahlborn
– jtahlborn, Commented Aug 13, 2016 at 3:48
@E.Wong your ORDER BY should come after the where clause change those places and check.. — Shakir Ahamed
– Shakir Ahamed, Commented Aug 13, 2016 at 4:12
Hmm, yea, I do have prepared statement, just not included in my question. Haha. xD Ah, I see, will do so and try it out. Thanks. :) — E. Wong
– E. Wong, Commented Aug 13, 2016 at 5:56

Rahul · Accepted Answer · 2016-08-13 03:54:45Z

2

Cause you have the WHERE and ORDER BY clauses in wrong places; not only that you actually have two WHERE clause in your query. It should be only one. WHERE should come first and then order by. It should rather be

WHERE CustomerName like "+ query +"
ORDER BY DueDate;

Also as a side note,

Consider using parameterized queries
Modify your query to use ANSI standard JOIN syntax instead old style syntax.

Your query should look like

SELECT c.CustomerName, r.Rental_ID, 
r.Staff_ID, r.RentalDate, 
r.DueDate, r.Customer_ID, f.Description
FROM Rental r  
JOIN FeedBack f ON f.Customer_ID = r.Customer_ID
JOIN Customer c ON c.Customer_ID = f.Customer_ID
WHERE c.CustomerName like "+ query +"
ORDER BY r.DueDate;

edited Aug 13, 2016 at 3:54

answered Aug 13, 2016 at 3:46

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

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

1 Comment

E. Wong Over a year ago

Ah, I see. My tutor taught me the old style syntax so I used that, but thanks for explaining. :)

Collectives™ on Stack Overflow

SQL query with user input

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related