CODEWITHSUNDEEP
sqlruby-on-railssql-serverrubysql-injection
ineedahero
ineedahero

Reputation: 527

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.

Upvotes: 0

Views: 155

Answers (1)

guzart
guzart

Reputation: 3730

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.

Upvotes: 1

Related Questions