使用预准备语句的变量列名称 [英] Variable column names using prepared statements

问题描述

我想知道是否还有使用预准备语句指定返回的列名。

I was wondering if there was anyway to specify returned column names using prepared statements.

我使用的是MySQL和Java。

I am using MySQL and Java.

当我尝试时：

String columnNames="d,e,f"; //Actually from the user...
String name = "some_table"; //From user...
String query = "SELECT a,b,c,? FROM " + name + " WHERE d=?";//...
stmt = conn.prepareStatement(query);
stmt.setString(1, columnNames);
stmt.setString(2, "x");

我得到这种类型的陈述（在执行前打印）。

I get this type of statement (printing right before execution).

SELECT a,b,c,'d,e,f' FROM some_table WHERE d='x'

我希望看到：

SELECT a,b,c,d,e,f FROM some_table WHERE d='x'

我知道对于表名，我不能这样做，正如所讨论的那样，
这里，但是想知道是否有某种方法可以为列名做。

I know that I cannot do this for table names, as discussed here, but was wondering if there was some way to do it for column names.

如果没有，那么我只需要尝试确保我消毒输入，因此它不会导致SQL注入漏洞。

If there is not, then I will just have to try and make sure that I sanitize the input so it doesn't lead to SQL injection vulnerabilities.

推荐答案

这表明数据库设计错误。用户不需要知道列名称。创建一个真实的数据库列，其中包含这些列名并改为存储数据。

This indicates a bad DB design. The user shouldn't need to know about the column names. Create a real DB column which holds those "column names" and store the data along it instead.

无论如何，不​​能，您不能将列名设置为 PreparedStatement 值。您只能将列值设置为 PreparedStatement 值

At any way, no, you cannot set column names as PreparedStatement values. You can only set column values as PreparedStatement values

如果您愿意继续朝这个方向发展，你需要清理列名并自己连接/构建SQL字符串。引用单独的列名并使用 String＃replace（） 以转义列名中的相同引号。

If you'd like to continue in this direction, you need to sanitize the column names and concatenate/build the SQL string yourself. Quote the separate column names and use String#replace() to escape the same quote inside the column name.

这篇关于使用预准备语句的变量列名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！