使用准备好的语句的变量列名

Variable column names using prepared statements(使用准备好的语句的变量列名)

问题描述

我想知道是否有任何方法可以使用准备好的语句指定返回的列名.

I was wondering if there was any way 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.

推荐答案

这表明一个糟糕的数据库设计.用户不需要知道列名.创建一个真正的 DB 列来保存那些列名".并将数据存储在其中.

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 值.您只能将列 values 设置为 PreparedStatement 值

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

如果您想继续朝这个方向发展，您需要清理列名(以避免 SQL 注入)并自己连接/构建 SQL 字符串.引用单独的列名并使用 String#replace() 转义列名中的相同引号.

If you'd like to continue in this direction, you need to sanitize the column names (to avoid SQL Injection) 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.

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