postgresql - /bookmarks 处的 PG::SyntaxError - 我无法弄清楚 SQL 查询错误的原因

Question

使用 sinatra 运行我的应用程序时，我收到错误消息PG::SyntaxError at /bookmarks ERROR: syntax error at or near "{" LINE 1: SELECT * FROM users WHERE id = {:id=>"5"} ^ 它发生在我单击/users/newroute 上的提交按钮时，然后应该将我带到 index route /。

回溯提供以下信息

/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 没有。

希望有人可以帮我解决这个问题。提前致谢。

标签： postgresqlsyntax-errorsinatrapg

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])

只传递期望的id值User.find。

您还让自己对 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)

...