Java 使用预准备语句的变量列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3135973/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Variable column names using prepared statements
提问by KLee1
I was wondering if there was anyway to specify returned column names using prepared statements.
我想知道是否可以使用准备好的语句指定返回的列名。
I am using MySQL and Java.
我正在使用 MySQL 和 Java。
When I try it:
当我尝试时：
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'
I would like to see however:
然而，我想看到：
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.
我知道我不能对表名执行此操作，正如此处所讨论的那样 ，但想知道是否有某种方法可以对列名执行此操作。
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.
如果没有，那么我将只需要尝试确保我清理输入，这样它就不会导致 SQL 注入漏洞。
采纳答案by BalusC
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.
这表明数据库设计不佳。用户不需要知道列名。创建一个真正的 DB 列来保存这些“列名”并沿它存储数据。
At any way, no, you cannot set column names as PreparedStatementvalues. You can only set column valuesas PreparedStatementvalues
无论如何，不，您不能将列名设置为PreparedStatement值。您只能将列值设置为PreparedStatement值
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.
如果您想继续朝这个方向发展，您需要清理列名（以避免SQL 注入）并自己连接/构建 SQL 字符串。引用单独的列名并用于String#replace()在列名内转义相同的引号。
回答by G__
I think this case can't work because the whole point of the prepared statement is to prevent the user from putting in unescaped query bits - so you're always going to have the text quoted or escaped.
我认为这种情况是行不通的，因为准备好的语句的重点是防止用户输入未转义的查询位 - 所以你总是要引用或转义文本。
You'll need to sanitize this input in Java if you want to affect the query structure safely.
如果您想安全地影响查询结构，您需要在 Java 中清理此输入。
回答by wgl
Prepare a whitelist of allowed column names. Use the 'query' to look up in the whitelist to see if the column name is there. If not, reject the query.
准备一个允许列名的白名单。使用“查询”在白名单中查找以查看列名是否存在。如果不是，则拒绝查询。
回答by Syed Abdul Khaliq
Use sql injection disadvantage of Statement Interface as advantage. Ex:
使用语句接口的 sql 注入劣势作为优势。前任：
st=conn.createStatement();
String columnName="name";
rs=st.executeQuery("select "+ columnName+" from ad_org ");
回答by Grbh Niti
Below is the solution in java.
下面是java中的解决方案。
String strSelectString = String.format("select %s, %s from %s", strFieldName, strFieldName2, strTableName);
回答by Grbh Niti
public void MethodName(String strFieldName1, String strFieldName2, String strTableName)
{
//Code to connect with database
String strSQLQuery=String.format("select %s, %s from %s", strFieldName, strFieldName2, strTableName);
st=conn.createStatement();
rs=st.executeQuery(strSQLQuery);
//rest code
}
回答by DraganescuValentin
The accepted answer is not actually correct. While the OP approach indicated a bad DB design, it might be required by the business logic (for instance a MySQL IDE)
接受的答案实际上并不正确。虽然 OP 方法表明数据库设计不好，但业务逻辑可能需要它（例如 MySQL IDE）
Anyway, for MySQL prepared statements, what you need to know is that ?is for values, but if you need to escape column names, table names etc, use ??instead.
无论如何，对于 MySQL 准备好的语句，您需要知道的是?值，但是如果您需要转义列名、表名等，请??改用。
Something like this will work:
像这样的事情会起作用：
SELECT ??, ??, ?? FROM ?? WHERE ?? < ?
Set values to ['id', 'name', 'address', 'user', 'id', 100]
将值设置为 ['id', 'name', 'address', 'user', 'id', 100]
