Sanitizing a Stored Procedure Call in SQL Statement

Question

I'm working in Rails, ActiveRecord, and Postgresql, and am attempting to sanitize a SQL statement that executes a stored procedure:

query =<<-SQL
    SELECT *
    FROM #{stored_procedure_name}
    (
        #{param_1},
        #{param_2}
    )
SQL

The above statement works when executed. From what I understand, the stored procedure should be sanitized at the database layer because of parametrization (due to the way I defined the function). However, how can I sanitize the query above? I tried ActiveRecord's sanitize methods to no avail by using something like

statement =<<-SQL
    SELECT * FROM ? (?, ?)
SQL

and then passing the parameters in, but this didn't work - and seems silly.

score 0 · Answer 1 · answered Oct 10 '16 at 14:41

0

This is how I have done this:

Create the string query just like you did, but replace the ? with named parameters, like this:
```
statement = <<-SQL
SELECT * FROM :procedure_name (:param_1, :param_2)
SQL
```

Now call your query like this:

YourModel.find_by_sql([
  statement,
  {
    procedure_name: 'your_procedure_name',
    param_1: 'your param 1 value',
    param_2: 'your second param value'
  }
])

This has worked for me to execute complex select queries, I haven't tried it for stored procedures, but I don't see why it shouldn't work.

answered Oct 10 '16 at 14:41

fanta

1,489
13
15

Unfortunately this throws an error `SELECT * FROM 'procedure_name'` because the function name is wrapped in single quotes – fibono Oct 10 '16 at 14:53
1

makes sense, let me see if I can find a way to fix that. – fanta Oct 10 '16 at 15:04
No, you can't fix this, the procedure name cannot be a parameter to the query. – Shadow Oct 10 '16 at 15:20
I forgot something about stored procedures in mysql, you should call them with `CALL`. – fanta Oct 10 '16 at 15:20
@fanta , this is in postgresql, should have clarified, my apologies – fibono Oct 10 '16 at 15:20
I see, so, to call a function in postgres you need to remove the `*`, I don't know if that's going to work though. And also remove the space between the function name and the parenthesis ?. – fanta Oct 10 '16 at 15:23
1

@fibono, you can do what this guy did http://stackoverflow.com/questions/27853371/rails-brakeman-warning-of-sql-injection – fanta Oct 10 '16 at 16:23
1

@fanta, yes, I actually ended up doing that. Also, the function name was stored in the db, so it's not passed in the params.Thank you! – fibono Oct 10 '16 at 17:06

Sanitizing a Stored Procedure Call in SQL Statement

1 Answers1