PG::SyntaxError at /bookmarks -我找不出SQL查询错误的原因

Question

当我使用sinatra运行我的应用程序时，当我点击/users/new路由上的提交按钮时，我得到了错误消息PG::SyntaxError at /bookmarks ERROR: syntax error at or near "{" LINE 1: SELECT * FROM users WHERE id = {:id=>"5"} ^ It happens，这应该会把我带到索引路由/。

回溯提供了以下信息

/Users/BartJudge/Desktop/Makers_2018/bookmark-manager-2019/lib/database_connection.rb in async_exec @connection.exec(sql)

/Users/BartJudge/Desktop/Makers_2018/bookmark-manager-2019/lib/database_connection.rb in query @connection.exec(sql)

/Users/BartJudge/Desktop/Makers_2018/bookmark-manager-2019/lib/user.rb in find result = DatabaseConnection.query("SELECT * FROM users WHERE id = #{id}") app.rb in block in <class:BookmarkManager> @user = User.find(id: session[:user_id])

这是database_connection文件

require 'pg'

class DatabaseConnection
  def self.setup(dbname)
    @connection = PG.connect(dbname: dbname)
  end

  def self.connection
    @connection
  end

  def self.query(sql)
    @connection.exec(sql)
  end
end

这是用户模型

require_relative './database_connection'
require 'bcrypt'

class User
  def self.create(email:, password:)
    encypted_password = BCrypt::Password.create(password
    )
    result = DatabaseConnection.query("INSERT INTO users (email, password) VALUES('#{email}', '#{encypted_password}') RETURNING id, email;")

    User.new(id: result[0]['id'], email: result[0]['email'])
  end

  attr_reader :id, :email

  def initialize(id:, email:)
    @id = id
    @email = email
  end

  def self.find(id)
    return nil unless id
    result = DatabaseConnection.query("SELECT * FROM users WHERE id = #{id}")
    User.new(
      id: result[0]['id'],
      email: result[0]['email'])
  end
end

这是控制器

require 'sinatra/base'
require './lib/bookmark'
require './lib/user'
require './database_connection_setup.rb'
require 'uri'
require 'sinatra/flash'
require_relative './lib/tag'
require_relative './lib/bookmark_tag'

class BookmarkManager < Sinatra::Base
  enable :sessions, :method_override
  register Sinatra::Flash

  get '/' do
    "Bookmark Manager"
  end
  get '/bookmarks' do
    @user = User.find(id: session[:user_id])
    @bookmarks = Bookmark.all
    erb :'bookmarks/index'
  end

  post '/bookmarks' do
    flash[:notice] = "You must submit a valid URL" unless     Bookmark.create(url: params[:url], title: params[:title])

    redirect '/bookmarks'
  end

  get '/bookmarks/new' do
    erb :'bookmarks/new'
  end

  delete '/bookmarks/:id' do
    Bookmark.delete(id: params[:id])
    redirect '/bookmarks'
  end

  patch '/bookmarks/:id' do
    Bookmark.update(id: params[:id], title: params[:title], url: params[:url])
    redirect('/bookmarks')
  end

  get '/bookmarks/:id/edit' do
    @bookmark = Bookmark.find(id: params[:id])
    erb :'bookmarks/edit'
  end

  get '/bookmarks/:id/comments/new' do
    @bookmark_id = params[:id]
    erb :'comments/new'
  end

  post '/bookmarks/:id/comments' do
    Comment.create(text: params[:comment], bookmark_id: params[:id])
    redirect '/bookmarks'
  end

  get '/bookmarks/:id/tags/new' do
    @bookmark_id = params[:id]
    erb :'/tags/new'
  end

  post '/bookmarks:id/tags' do
    tag = Tag.create(content: params[:tag])
    BookmarkTag.create(bookmark_id: params[:id], tag_id: tag.id)
    redirect '/bookmarks'
  end

  get '/users/new' do
    erb :'users/new'
  end

  post '/users' do
    user = User.create(email: params[:email], password: params[:password])
    session[:user_id] = user.id
    redirect '/bookmarks'
  end

  run! if app_file == $0
end

在用户模型中，self.find(id)是可能有问题的SQL查询所在的位置。

我已经尝试过了；"SELECT * FROM users WHERE id = #{id}“

和"SELECT * FROM users WHERE id = '#{id}'“

除此之外，我被难住了。查询看起来很好，但是sinatra没有。

希望有人能帮我解决这个问题。谢谢，提前说好。

Answer 1

您正在使用散列参数调用find：

User.find(id: session[:user_id])

但它期望的只是id

class User
  ...
  def self.find(id)
    ...
  end
  ...
end

然后，您最终在SQL字符串中插入一个散列，这将导致无效的HTML。

你应该说：

@user = User.find(session[:user_id])

仅传入User.find期望的id。

您还可能会遇到SQL注入问题，因为您在查询中使用的是不受保护的字符串插值，而不是占位符。

您的query方法应该使用exec_params而不是exec，并且它应该为占位符值使用一些额外的参数：

class DatabaseConnection
  def self.query(sql, *values)
    @connection.exec_params(sql, values)
  end
end

然后，调用query的东西应该在SQL中使用占位符，并分别传递这些值：

result = DatabaseConnection.query(%q(
  INSERT INTO users (email, password)
  VALUES($1, $2) RETURNING id, email
), email, encypted_password)

result = DatabaseConnection.query('SELECT * FROM users WHERE id = $1', id)

...