Assign variable to mysql query ruby

Question

So I'm building a login/signup page in Ruby/Sinatra, and I'm trying to add some logic so that if someone tries to sign up with an email that is in use, it will tell them so, and not allow them to sign up

require 'rubygems'
require 'sinatra'
require 'mysql'

get "/" do
        erb :form
end

post "/" do

begin
        con = Mysql.new('localhost', 'tut', 'tut', 'recruited_users')
        auth = con.query('SELECT school FROM users WHERE email = "#{params[:email]}" AND password = "#{params[:password]}"')
        auth.fetch_row
        ensure
                con.close if con
        end
end

get '/signup' do
        erb :signup
end

post '/signup' do

begin
        con = Mysql.new('localhost', 'tut', 'tut', 'recruited_users')
        check_for_user = con.query("SELECT email FROM users WHERE email = '#{params[:email]}'")
        if check_for_user == ''
                "Sorry, but there is already an account for this user. The ID is '#{params[:check_for_user]}', please try again"
        else
                auth = con.query("INSERT INTO users (email, password, school) VALUES('#{params[:email]}', '#{params[:password]}', '#{params[:school]}')")
                "Succesfully created user #{params[:email]}"
        end
        ensure
                con.close if con
        end
end

The problem is that the variable check_for_user is not receiving any value, at least not one that I can work with. I need to be able to set up the if statement so that they can only create a new user if the email does not already exist in the database.

Just out of interest, what is making you choose raw SQL rather than an ORM like sequel or datamapper? — djlumley
– djlumley, Commented Sep 3, 2012 at 5:41

Martin T. · Accepted Answer · 2018-10-04 17:38:28Z

3

First of all, you can't use string interpolation (#{...}) inside a single quoted string, that only works with double quoted strings (or things like %Q{...} that behave like double quoted strings). Secondly, string literals in SQL should be quoted with single quotes, MySQL and SQLite let you get away with double quotes but that's a bad habit. Thirdly, we're not hacking PHP in 1999 so you shouldn't be using string interpolation to build SQL, you should use placeholders:

sth = con.prepare('select school from users where email = ? and password = ?')
sth.execute(params[:email], params[:password])
row = sth.fetch
if(!row)
    # Didn't find anything
else
    # The school is in row.first
end

edited Oct 4, 2018 at 17:38

Martin T.

5476 silver badges11 bronze badges

answered Sep 3, 2012 at 5:02

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

3 Comments

the Tin Man Over a year ago

In addition, the user could enter their string in a different letter-case than the initial email address. The case-sensitive search would return a miss, but folding to the same case in the database and the search, would return a hit.

OneChillDude Over a year ago

I tried the above code, and I'm still not getting the right result. Is there something I need to do to use the ? placeholder?

mu is too short Over a year ago

@bwheeler96: what does "not working" mean? Any errors or exceptions? Do you have a case problem as Tin Man noted? Have you tried your queries in the mysql tool?

Collectives™ on Stack Overflow

Assign variable to mysql query ruby

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related