ruby-on-rails - 在 postgres 中插入值，但它们被解释为列

Question

我正在尝试像这样在我的 postgres 数据库中插入一些数据:

  def insert_row(conn, row)
    attendee = map_row_to_struct(row)

    conn.execute(
      <<-SQL
        INSERT INTO tmp_attendee_import (email, first_name, last_name)
        VALUES("#{attendee.email}", "#{attendee.first_name}", "#{attendee.last_name}");
      SQL
    )
  end

SQL 被评估为:

INSERT INTO tmp_attendee_import (email, first_name, last_name)
VALUES("myemail@yahoo.com", "Gigel", "Ion");

但是我得到这个错误:

Failure/Error:
               conn.execute(
                 <<-SQL
                   INSERT INTO tmp_attendee_import (email, first_name, last_name)
                   VALUES("#{attendee.email}", "#{attendee.first_name}", "#{attendee.last_name}");
                 SQL
               )

     ActiveRecord::StatementInvalid:
       PG::UndefinedColumn: ERROR:  column "myemail@yahoo.com" does not exist
       LINE 2:             VALUES("myemail@yahoo.com", "Gigel", "Ion");
                                  ^
       :             INSERT INTO tmp_attendee_import (email, first_name, last_name)
                   VALUES("myemail@yahoo.com", "Gigel", "Ion");

有人知道吗？

Answer 1

Postgres manual

A string constant in SQL is an arbitrary sequence of characters bounded by single quotes ('), for example 'This is a string'. To include a single-quote character within a string constant, write two adjacent single quotes, e.g., 'Dianne''s horse'. Note that this is not the same as a double-quote character (").

There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). A delimited identifier is always an identifier, never a key word. So "select" could be used to refer to a column or table named "select", whereas an unquoted select would be taken as a key word and would therefore provoke a parse error when used where a table or column name is expected.

TL;DR:字符串常量用单引号，表/列名用双引号。

顺便说一句，您选择的插入记录的方式容易受到 sql-injection 的攻击。 .