Forgive me for asking what seems like a stupid question, but I'm trying to figure out if there's a security risk in my project. I'm trying to implement a highly flexible search feature in my project as simply as possible. I have a MySQL server with a single database containing four tables, and a Java servlet that talks to the database. The servlet connects using regular JDBC calls, authenticating as user "search". The "search" user only has SELECT rights, and only to tables that contain public data.
My question is: would allowing arbitrary SQL queries to be passed to the servlet and executed with the rights of the "search" user be a security risk? My first instinct would be no, since that user only has SELECT rights, and only on tables that contain public data. I just wanted to run it by people who know a lot more about this than me. Is this exactly the type of situation limited rights are designed for?
If you're having a hard time understanding what a possible scenario would be, I might want to perform a search using an XHR to the servlet, returning XML data to my script:
http://my.website.domain/servlet/search?query=SELECT+*+FROM+%60tableA%60+WHERE+%60id%60+IN+(SELECT+DISTINCT+%60tableA%60+FROM+%60tableB%60+WHERE+FIND_IN_SET(%60user%60%2c+'somebody';))
...which would just run the query:
SELECT * FROM `tableA` WHERE `id` IN (SELECT DISTINCT `tableA` FROM `tableB` WHERE FIND_IN_SET(`user`, 'somebody'))
Any suggestions/comments would be appreciated. Thanks!
-Will