I have a query like this in MySQL
val selectQ = "SELECT NAME FROM EMPLOYEE" val date = "2010-10-10" val age = 10
Now I have some dynamic AND clauses, like
val whereNameFilter = "WHERE date = $date" val andAgeFilter = "AND age = $age"
I concat whereNameFilterand andAgeFilter based on conditions because sometimes I dont need to concat it at all
I don’t want to use ORMs as the ORM’s have proved slow performance and want to achieve in Plain SQL as it has proved high performance. Similarly I have some subqueries which is dynamic based on the input fields. Assuming that all my inputs are sanitized and relevant errors are thrown, before the query construction, what is the chance of SQL injection ?
If there is a chance, what are the ways to prevent it ? Is there any utility which helps me to construct query dynamically with dynamic AND / OR / IN and dynamic subqueries ?
My programming language is Scala
Advertisement
Answer
There’s a difference between strings that you have control over by hard-coding them in your app, versus strings that come from untrusted sources.
I am not a Scala programmer, so I’ll form this like pseudocode:
conditions = Array()
params = Array()
selectQ = "SELECT NAME FROM EMPLOYEE WHERE true
if (date_input) {
selectQ += "AND date = ?"
params.append(date_input)
}
if (age_input) {
selectQ += "AND age = ?"
params.append(age_input)
}
query = prepare(selectQ)
fore (params as i=>p) {
query.setString(i, p)
}
This technique ensures that only strings you have hard-coded are appended to your SQL query.
Other content is combined as bound parameters. Query parameters are NOT just appended to the query, they are kept separate until after the prepare() of the query. After that, the query syntax has already been parsed by the SQL engine, so no content can affect the parsing.
Re your comment:
If the expressions themselves are part of untrusted content (including, but not limited to user input), then interpolating them verbatim into your SQL query is of course SQL injection. You must not do this.
You can whitelist the expressions. That is, compare them to a list of known accepted expressions, and if they aren’t in that list, reject them.
Even better is to allow the client to select a known safe expression by some identifier. For example, the client would pass an integer 7, and that would correspond to “AND age = ?” that is hard-coded in your app.
If you really can’t restrict the variety of expressions that are accepted, and you can’t whitelist them, then yes, that’s SQL injection by design. This will never be a safe application. In my opinion, you should not implement code that does this.
It’s like if you were an electrician instead of a software engineer, and your employer told you to run some uninsulated wires through a bucket of oily rags. I would think it’s your professional responsibility to refuse to follow their orders. They might sack you and find someone else to do it, but at least you would not be responsible for causing the inevitable house fire.