SQLite query fails without error in Ruby

Question

I have a function that finds an oid from a field in a table. According to the docs, the oid field is automatically created and auto incremented. Sounds great.

def teddy_bear_id_by_url(url)
  query = "select oid from teddy_bears where url = \"#{url}\""
  res = $db.execute(query)
  return res
end

Unfortunately this code returns a [] (empty array), when running the query in the sqlite shell gives a 'good' value (e.g. 4).

def teddy_bear_id_by_url(url)
  return $db.execute("select oid from teddy_bears where url = '?'", url)
end

The above doesn't work either.

I did indeed check that urlcontains what I think it does.

What might be happening?

Are you sure your url contains what you think it does? No leading or trailing whitespace? What happens if you leave the where clause out? And as an aside, you really should be using single quotes for string literals in SQL (SQLite allows double quotes but that just leads to bad habits) and you should be using placeholders instead of pretending you're a PHP programmer in 1999. — mu is too short
– mu is too short, Commented Jul 16, 2012 at 4:29
url does indeed contain what I think it does. I logged it, and it returns precisely what it should. No leading or trailing whitespace. I replaced it with single quotes and still no dice. Thanks for the placeholders tips, but I did indeed try with placeholders with the same results. — tekknolagi
– tekknolagi, Commented Jul 16, 2012 at 5:07
The placeholder version would be $db.execute("select oid from teddy_bears where url = ?", url) (i.e. no quotes around the placeholder). — mu is too short
– mu is too short, Commented Jul 16, 2012 at 5:57
I need the quotes, unfortunately, since url has or could have spaces. The one with which I'm testing has spaces. — tekknolagi
– tekknolagi, Commented Jul 16, 2012 at 6:15
The placeholder will only be recognized without the quotes, using a placeholder takes care of all the quoting and escaping issues for you (which is the big reason to use them). — mu is too short
– mu is too short, Commented Jul 16, 2012 at 6:33

mu is too short · Accepted Answer · 2012-07-16 07:16:14Z

1

There's probably something funny going on in your url that, combined with your string interpolation, is messing up your SQL. Your best bet is to use placeholders with your execute call:

$db.execute("select oid from teddy_bears where url = ?", url)

answered Jul 16, 2012 at 7:16

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tekknolagi Over a year ago

Awesome answer, but unfortunately one more problem: the results are coming back blank (still). I queried with: ` res = $db.execute("select oid from teddy_bears where url = ?", url)` and checked with ` $log.debug res.inspect`.

mu is too short Over a year ago

@tekknolagi: Have you tried just $db.execute("select oid from teddy_bears") or with another url? You say that the URL has spaces in it, have they been encoded as %20 or + in your url variable in Ruby or the SQLite database?

Collectives™ on Stack Overflow

SQLite query fails without error in Ruby

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related