使用预准备语句的变量列名 [英] 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.

推荐答案

这表明数据库设计不好.用户不需要知道列名.创建一个真正的 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.

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