ineedahero ineedahero - 1 month ago 8
Ruby Question

Rails: Still confused about SQL Injection

I've made some posts about translating user input from a search box into an ActiveRecord Parameterized statement, which may be vulnerable to SQL injection, but I think I may have a solution.

Here's how the search works. The user enters something like this into the search box:

name="hello" AND NOT address.town="Villa"


Internally, I convert it to:

query = ["name LIKE ? AND address.town NOT LIKE ?", "hello", "villa"]


for:

if search
query = convert_search_to_query search
begin
includes(:address).where(query)
# rescue Exception ...
...
...


Here's my idea: simply check the user-inputted attributes ("name", "address.town" in this case) to make sure it's an exact match for the acceptable list of user attributes.

If I were to do this, I think that there would be no SQL Injection possible since I am using parameterized statements (with the '?') to handle the only part of the user's input I can't check -- the values he entered for each attribute.

Based on what I read from other posts on here, I don't see how this code could be any more vulnerable than a normal parameterized search, but I don't have a lot of experience with SQL injection. Is it vulnerable?

Also:

I understand that there are plugins that may be able to help, but what I want to do is really very simple, and is already working, and I'd rather keep my app as lightweight as possible.

Answer

ActiveRecord will prevent any SQL injection attacks, AS LONG AS you are using the parameterized form. As a rule of thumb, ALL information coming from the user should be a parameter.

In your example you mention converting the user query into:

where(["name LIKE ? AND address.town NOT LIKE ?", "hello", "villa"])

In this case ActiveRecord will protect hello and villa from SQL injection, but it will NOT protect name LIKE ? AND address.town NOT LIKE ?. ActiveRecord assumes that name LIKE ? AND address.town NOT LIKE ? is being generated either by the developer (hard coded) or by the application, either way it assumes it's safe to execute.

So if any part of name LIKE ? AND address.town NOT LIKE ? is coming from the user your app could be vulnerable to SQL injection attacks.

The proper way to do it would be to use a language parser to completely decompose the user query and then re-generate it as a safe query. Using Regex to match and replace could be a naive approach unless you are a master in Regex and security.