How to prepare SQL statement without using where() method

Question

Is there a way to prepare an SQL statement like that built by ActiveRecord where() method (using named placeholders):

Client.where("created_at >= :start_date AND created_at <= :end_date",
{start_date: params[:start_date], end_date: params[:end_date]})

I have to use this in CASE .. END statement after ORDER BY clause (or in SELECT to create a computed column) to protect it from SQL injection.

EDIT:
I have to retrieve all the ActiveRecord models by this query too. So can I use find_by_sql()? (trying...).

EDIT2: find_by_sql() can't use named placeholders (:start_date in the above code example).

It's Rails 3.2.11

EDIT3:
Sorry, it can use with an Array as one parameter (find_by_sql with array format in Rails 3).

Community · Accepted Answer · 2017-05-23 10:29:21Z

Yes, you can write / prepare anything that ActiveRecord does. Do you mean to use a direct DBI query?

I recommend you trace the SQL that ActiveRecord is generating.

Retrieving sql queries from Active-record queries in Rails 3

Tracing Rails 3 SQL queries

http://guides.rubyonrails.org/v2.3.11/debugging_rails_applications.html

See query_trace for one approach.

A tutorial on how to use Ruby DBI and prepared statements:

http://www.tutorialspoint.com/ruby/ruby_database_access.htm

Quoted directly from the tutorial:

#!/usr/bin/ruby -w

require "dbi"

begin
 # connect to the MySQL server
 dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", 
                    "testuser", "test123")
 sth = dbh.prepare( "INSERT INTO EMPLOYEE(FIRST_NAME,
               LAST_NAME, 
               AGE, 
       SEX, 
       INCOME)
               VALUES (?, ?, ?, ?, ?)" )
 sth.execute('John', 'Poul', 25, 'M', 2300)
 sth.execute('Zara', 'Ali', 17, 'F', 1000)
 sth.finish
 dbh.commit
 puts "Record has been created"
rescue DBI::DatabaseError => e
 puts "An error occurred"
 puts "Error code:    #{e.err}"
 puts "Error message: #{e.errstr}"
 dbh.rollback
ensure
 # disconnect from server
 dbh.disconnect if dbh
end

Saran S. · Accepted Answer · 2014-05-23 06:07:05Z

0

Using find_by_sql() solved this.

But this can't be use as scoped query. (And thus cannot be chained).

edited May 23, 2014 at 6:07

answered May 23, 2014 at 5:45

Saran S.

2674 silver badges12 bronze badges

Collectives™ on Stack Overflow

How to prepare SQL statement without using where() method

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related