How to run raw SQL queries with Sequel

Question

I am not clear yet on the proper way to run raw SQL queries with Sequel.

Currently I am trying this:

DB.fetch("SELECT * FROM zone WHERE dialcode = '#{@dialcode}' LIMIT 1") do |row|
 @zonename = row
end

How can I can run the queries as raw SQL then access the results like normal?

if @zonename.name = "UK"

score 22 · Answer 1 · edited Oct 30 '16 at 23:08

22

Note that instead of:

DB.fetch("SELECT * FROM zone WHERE dialcode = '#{@dialcode}' LIMIT 1")

you should do:

DB.fetch("SELECT * FROM zone WHERE dialcode = ? LIMIT 1", @dialcode)

Otherwise, you open yourself to SQL injection if you don't control the contents of @dialcode.

edited Oct 30 '16 at 23:08

the Tin Man

158,662
42
215
303

answered Aug 17 '12 at 14:53

Jeremy Evans

11,959
27
26

Could you tell me what method is called by `fetch` under the hood to escape the user input? I have a complex query that is built up with many methods and need to escape the user input separately from my database call, while building the query. – Mike H-R Apr 24 '14 at 07:23
sorry, just found it in the docs (which I was reading while searching this, will post it here for others): https://github.com/jeremyevans/sequel/blob/cf1e987c1c2f7e74dca5c0f8312149bede659a21/doc/security.rdoc . The method is `DB.literal(my_insecure_string)`. Thanks for the library Jeremy. – Mike H-R Apr 24 '14 at 07:26

Chris McCauley · Accepted Answer · 2022-02-25T17:07:24.693

I have a few pointers which may be useful:

You could simply do:

 @zonename = DB.fetch("SELECT * FROM zone WHERE dialcode = ? LIMIT 1", @dialcode).first

NB: you are ignoring the fact that there could be more results matching the criteria. If you expect multiple possible rows to be returned then you probably want to build an array of results by doing ...

    @zonename = DB.fetch("SELECT * FROM zone WHERE dialcode = ? LIMIT 1", @dialcode).all

and processing all of them.

The return set is a hash. If @zonename points to one of the records then you can do
```
 @zonename[:column_name] 
```

to refer to a field called "column_name". You can't do @zonename.column_name (you could actually decorate @zonename with helper methods using some meta-programming but let's ignore that for the moment).

Sequel is an excellent interface, the more you learn about it the more you'll like it.

How to run raw SQL queries with Sequel

2 Answers2

Linked