在psycopg2中将表名作为参数传递 [英] Passing table name as a parameter in psycopg2

问题描述

我有以下代码，使用pscyopg2:

I have the following code, using pscyopg2:

sql = 'select %s from %s where utctime > %s and utctime < %s order by utctime asc;'
data = (dataItems, voyage, dateRangeLower, dateRangeUpper)
rows = cur.mogrify(sql, data)

这将输出:

select 'waterTemp, airTemp, utctime' from 'ss2012_t02' where utctime > '2012-05-03T17:01:35+00:00'::timestamptz and utctime < '2012-05-01T17:01:35+00:00'::timestamptz order by utctime asc;

执行此操作时，它会掉下来-这是可以理解的，因为表名周围的引号是非法的.

When I execute this, it falls over - this is understandable, as the quotes around the table name are illegal.

是否有一种方法可以合法地将表名作为参数传递，或者我是否需要进行(明确警告)字符串连接，即:

Is there a way to legally pass the table name as a parameter, or do I need to do a (explicitly warned against) string concatenation, ie:

voyage = 'ss2012_t02'
sql = 'select %s from ' + voyage + ' where utctime > %s and utctime < %s order by utctime asc;'

为任何见识加油.

推荐答案

表名不能作为参数传递，但其他所有参数都可以.因此，表名应在您的应用程序中进行硬编码(不要接受输入或使用程序外的任何东西作为名称).您拥有的代码应对此起作用.

The table name cannot be passed as a parameter, but everything else can. Thus, the table name should be hard coded in your app (Don't take inputs or use anything outside of the program as a name). The code you have should work for this.

如果您有合理的理由使用外部表名称，请确保您不允许用户直接输入该名称.也许可以传递索引来选择表，或者可以通过其他方式查找表名.但是，您应该对此保持警惕.之所以可行，是因为表名相对较少.找到一种验证表名的方法，您应该会很好.

On the slight chance that you have a legitimate reason to take an outside table name, make sure that you don't allow the user to directly input it. Perhaps an index could be passed to select a table, or the table name could be looked up in some other way. You are right to be wary of doing this, however. This works, because there are relatively few table names around. Find a way to validate the table name, and you should be fine.

可以执行类似的操作，以查看表名称是否存在.这是参数化版本.只需确保您执行此操作并在运行SQL代码之前验证输出即可.对此的部分想法来自此答案.

It would be possible to do something like this, to see if the table name exists. This is a parameterised version. Just make sure that you do this and verify the output prior to running the SQL code. Part of the idea for this comes from this answer.

SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' and table_name=%s LIMIT 1

这篇关于在psycopg2中将表名作为参数传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！