What are bind parameters
Bind parameters are a way to pass values into the SQL statement without embedding the values into the SQL as literals. The values are provided to the SQL statement through parametrization or using a separate API call.
There are two benefits of using bind parameters:
- Security - Preventing injection attacks by quoting
- Performance - Improving performance using prepared statements
Security
Bind parameters are the most effective way to fix SQL injection issues. When you embed the user-supplied literals into the query, Active Record cannot perform quoting on it. But when the literals are provided as a parameter, Active Record will quote the literals to prevent injection attacks.
# User supplied sha
short_sha = "e92'1a1c"
# Unsafe
Post.where("sha LIKE '%#{short_sha}%'").to_sql
=> "SELECT \"posts\".* FROM \"posts\" WHERE (sha LIKE '%e92'1a1c%')"
# Safe - Notice the doubling up of the quote character
Post.where("sha LIKE ?", "%#{short_sha}%")
=> "SELECT \"posts\".* FROM \"posts\" WHERE (sha LIKE '%e92''1a1c%')"
Performance
When a parameterized query is executed, ActiveRecord would first prepare the query and later execute it with different parameters. This way the database would be able to reuse the execution plan without the need to parse and optimize the SQL statement each time.
Let’s take an example:
> ActiveRecord::Base.connection.execute("select * from pg_prepared_statements").values
(1.0ms) select * from pg_prepared_statements
=> []
# bind params are not used in [where strings](https://github.com/rails/rails/pull/31812)
# they are also not used in [where(id: [1, 2, 3]](https://github.com/rails/rails/commit/cbcdecd2)
> Commit.where("sha = 'e921a1cc21193902a84016d633a9e10d82555bbd'")
Commit Load (3.7ms) SELECT "commits".* FROM "commits" WHERE (sha = 'e921a1cc21193902a84016d633a9e10d82555bbd')
=> ...
# PostgreSQL prepared statement cache is empty
> ActiveRecord::Base.connection.execute("select * from pg_prepared_statements").values
(0.7ms) select * from pg_prepared_statements
=> []
> Commit.where(sha: 'e921a1cc21193902a84016d633a9e10d82555bbd')
Commit Load (1.3ms) SELECT "commits".* FROM "commits" WHERE "commits"."sha" = $1 [["sha", "e921a1cc21193902a84016d633a9e10d82555bbd"]]
=> ...
# the previous SQL query has been prepared
> ActiveRecord::Base.connection.execute("select * from pg_prepared_statements").values
(0.9ms) select * from pg_prepared_statements
=> [["a1", "SELECT \"commits\".* FROM \"commits\" WHERE \"commits\".\"sha\" = $1", 2019-10-17 12:24:35 +0000, "{text}", false]]
Executing custom SQL
When you write a query using find_by_sql with the array syntax,
rails will only perform sanitization on the query and not use prepared statements.
> MONTH_WITH_YEAR_SQL = Arel.sql(
<<-SQL.squish
to_char(
date_trunc('month', posts.created_at),
'FMMonth YYYY'
)
SQL
)
> Post.find_by_sql([
"select posts.* from posts where #{MONTH_WITH_YEAR_SQL} = :month_and_year",
{month_and_year: "August 2019"}
])
=> ...
> ActiveRecord::Base.connection.execute("select * from pg_prepared_statements").values
(1.2ms) select * from pg_prepared_statements
=> []
Now, the question is how to make find_by_sql to use prepared statements.
The answer lies in the method signature of find_by_sql.
But before that let’s look at how to pass bind params to find_by_sql.
# passing binds as an array of name, value pairs
> Post.find_by_sql(
"select posts.* from posts where #{MONTH_WITH_YEAR_SQL} = $1",
[[nil, "August 2019"]]
)
Post Load (0.6ms) select posts.* from posts where to_char( date_trunc('month', posts.created_at), 'FMMonth YYYY' ) = $1 [[nil, "August '2019"]]
=> ...
> binds = [
ActiveRecord::Relation::QueryAttribute.new(
nil, # name
"August 2019", # value
ActiveRecord::Type::Text.new # type
)
]
# passing binds as an array of QueryAttribute objects
> Post.find_by_sql(
"select posts.* from posts where #{MONTH_WITH_YEAR_SQL} = $1",
binds
)
Post Load (0.6ms) select posts.* from posts where to_char( date_trunc('month', posts.created_at), 'FMMonth YYYY' ) = $1 [[nil, "August '2019"]]
=> ...
The third parameter to find_by_sql when true creates the prepared statement
in the database.
> Post.find_by_sql(
"select posts.* from posts where #{MONTH_WITH_YEAR_SQL} = $1",
[[nil, "August 2019"]],
preparable: true
)
=> ...
ActiveRecord::Base.connection.execute("select * from pg_prepared_statements").values
=> [["a1", "select posts.* from posts where to_char( date_trunc('month', posts.created_at), 'FMMonth YYYY' ) = $1", 2019-10-17 14:11:04 +0000, "{text}", false]]
But if we look at the code flow inside ActiveRecord,
it will no longer perform sanitization.
sanitize_sql is called on the
first ( sql ) parameter,
which calls sanitize_sql_array when the
passed parameter is an Array.
Now we need to sanitize the data ourselves before handing
it over to find_by_sql.
# Notice the dangling single quote before the year
> Post.find_by_sql(
"select posts.* from posts where #{MONTH_WITH_YEAR_SQL} = $1",
[[nil, "August '2019"]],
preparable: true
)
Post Load (0.6ms) select posts.* from posts where to_char( date_trunc('month', posts.created_at), 'FMMonth YYYY' ) = $1 [[nil, "August '2019"]]
=> ...
# Notice the single quote has been doubled up
> Post.find_by_sql(
"select posts.* from posts where #{MONTH_WITH_YEAR_SQL} = $1",
[[nil, ActiveRecord::Base.connection.quote("August '2019")]],
preparable: true
)
Post Load (0.5ms) select posts.* from posts where to_char( date_trunc('month', posts.created_at), 'FMMonth YYYY' ) = $1 [[nil, "'August ''2019'"]]
=> ...
Example using exec_query
> ApplicationRecord.connection.exec_query(
'SELECT * FROM projects WHERE id = $1',
'SQL',
[[nil, "30d90923-c51b-4f15-8043-ff1d7eca960b"]],
prepare: true
)
SQL (1.0ms) SELECT * FROM projects WHERE id = $1 [[nil, "30d90923-c51b-4f15-8043-ff1d7eca960b"]]
=> ...
# the second parameter name is what appears in the log
> ApplicationRecord.connection.exec_query(
'SELECT * FROM projects WHERE id = $1',
'-- CUSTOM SQL --',
[[nil, "30d90923-c51b-4f15-8043-ff1d7eca960b"]],
prepare: true
)
-- CUSTOM SQL -- (0.6ms) SELECT * FROM projects WHERE id = $1 [[nil, "30d90923-c51b-4f15-8043-ff1d7eca960b"]]
=> ...
