Using a variable in Ruby Mysql query

Question

I have a name stored in the variable username and would like to pull users row information when I try

result = dbh.query("SELECT * FROM maintab WHERE user = '#{username}'")

I get no results. If I put in the username by hand however, it does return a result. How format my query so that I may use variables?

Thanks for the warning, I'll be sure to injection attack proof all my queries once I actually get everything working. — Arbiter
– Arbiter, Commented Apr 1, 2011 at 7:27
I would recommend using something a little more high level like github.com/jeremyevans/sequel DB[:maintab].filter(:user => username) or DB['select * from maintab where user = ?', username] — Jonas Elfström
– Jonas Elfström, Commented Apr 1, 2011 at 8:34

fl00r · Accepted Answer · 2011-04-01 07:35:51Z

3

Try to debug this way:

username = "Peter" # any of your real name
result = dbh.query("SELECT * FROM maintab WHERE user = '#{username}'")

it should work. Looks like your username is nil or blank

answered Apr 1, 2011 at 7:35

fl00r

83.7k33 gold badges222 silver badges239 bronze badges

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

3 Comments

Arbiter Over a year ago

Crazy: I was putting the username in via gets on screen and output the variable just to make sure it was there and it was. However when I set username like you did above it worked. Turns out there was something extra on the variable and .strip clears it right up. Thanks.

mu is too short Over a year ago

@Arbiter: You were saying something about getting it working before worrying about data cleansing (i.e. SQL injection)...

Arbiter Over a year ago

I was stupidly assuming that the gem just had trouble with using variables in queries. That'll teach me...

BobMcJob · Accepted Answer · 2011-04-01 07:09:41Z

0

Open up IRB and try to print what you have.

How does #{} behave with single quotes vs escaped double quotes?

That should answer your question.

answered Apr 1, 2011 at 7:09

BobMcJob

1

1 Comment

Arbiter Over a year ago

I'm not exactly sure if I can try the IRB. Im running a script on a remote server. I have it put out the number of rows it finds to see if it found anything. I believe I correctly tried the escaped double quotes and met the same result. Its very possible that I did it incorrectly though

Collectives™ on Stack Overflow

Using a variable in Ruby Mysql query

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related