在postgres中插入值，但它们被解释为列 [英] Inserting values in postgres but they are interpreted as column

问题描述

我试图像这样在Postgres数据库中插入一些数据：

I am trying to insert some data in my postgres databae like this:

  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的奇妙评价为：

The SQL is evaluated wonderfully as:

INSERT INTO tmp_attendee_import (email, first_name, last_name)
VALUES("[e-mail]", "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 "[e-mail]" does not exist
       LINE 2:             VALUES("[e-mail]", "Gigel", "Ion");
                                  ^
       :             INSERT INTO tmp_attendee_import (email, first_name, last_name)
                   VALUES("[e-mail]", "Gigel", "Ion");

任何人都有任何线索吗？

Anyone has any clue ?

推荐答案

Postgres手册

SQL中的字符串常量是由单引号（' ），例如这是一个字符串。要使
在字符串常量中包含单引号字符，请在相邻的两个单引号之间加上两个
，例如戴安娜的马。 请注意，这不同于
与双引号字符（）。

还有另一种标识符：带分隔符的标识符或带引号的
，它是通过将任意
字符括在双引号（）中形成的。分隔标识符始终是
标识符，而不是关键字。因此，选择可用于引用名为选择的
列或表，而未引用的选择将
作为关键字，因此当
时会引发解析错误

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：字符串常量的单引号，表的双引号/列名称。

BTW，您选择的插入记录的方法容易受到 sql注入。

BTW, the way you're choosing for inserting records is vulnerable to sql-injection.

这篇关于在postgres中插入值，但它们被解释为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！