mysql - 将变量分配给mysql查询ruby

Question

所以我在 Ruby/Sinatra 中构建了一个登录/注册页面，我试图添加一些逻辑，这样如果有人试图使用正在使用的电子邮件注册，它会告诉他们，并且不允许他们注册

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

问题是变量 check_for_user 没有收到任何值，至少不是我可以使用的值。我需要能够设置 if 语句，以便他们只能在数据库中尚不存在电子邮件时创建新用户。

Answer 1

首先，您不能在单引号字符串中使用字符串插值 (#{...})，它只适用于双引号字符串(或诸如 % Q{...} 的行为类似于双引号字符串)。其次，SQL 中的字符串文字应该用单引号引起来，MySQL 和 SQLite 让你不用双引号，但这是一个坏习惯。第三，我们不是在 1999 年破解 PHP，所以你不应该使用字符串插值来构建 SQL，你应该使用占位符:

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