gpt4 book ai didi

java - SQL Like 查询错误

转载 作者:行者123 更新时间:2023-12-02 05:44:44 25 4
gpt4 key购买 nike

我有一个带有 sql 选择查询的结果集:

ResultSet rst = DB.search("select '"+col+"' from stud where '"+col+"' like '" + S3 + "%'");

这里 col = FName(FName 是一列);

以下是如何将 FName 分配给 col :

private void column(){
switch (search_fields.getSelectedItem().toString()) {
case "FName":
col = "FName";
break;
case "MName":
col="MName";
break;
case "LName":
col="LName";
break;
case "DOB":
col="DOB";
break;
case "Address":
col="Address";
break;
case "MotherTP":
col="MotherTP";
break;
case "FatherTP":
col="FatherTP";
break;
case "School":
col="School";
break;
case "Grade":
col="Garde";
break;
case "Email":
col="Email";
break;
}
}

Search_field 是一个组合框。没有错误,但当我输入 First Name(FName) 时,会返回列的名称 FName

这是完整的代码:

private JTextField txtComboItemName;
private String S3;
private boolean bbb;
private void ComboItemSearch() {
bbb = false;
txtComboItemName = (JTextField) search_txt.getEditor().getEditorComponent();
txtComboItemName.addKeyListener(new KeyAdapter() {
@Override
public void keyReleased(KeyEvent evt) {
if (!(
evt.getKeyCode() == KeyEvent.VK_DOWN ||
evt.getKeyCode() == KeyEvent.VK_UP ||
evt.getKeyCode() == KeyEvent.VK_LEFT ||
evt.getKeyCode() == KeyEvent.VK_RIGHT ||
evt.getKeyCode() == KeyEvent.VK_ENTER)) {
try {
S3 = txtComboItemName.getText();

ResultSet rst = DB.search("select '"+col+"' from stud where '"+col+"' like '" + S3 + "%'");
System.out.println("col:"+ col);
boolean b = rst.next();

boolean bb = false;
if (b) {
search_txt.removeAllItems();
bb = true;
}
while (b) {
if (rst.getString(col).startsWith(S3)) {
search_txt.addItem(rst.getString(1));
}

b = rst.next();
}

search_txt.setSelectedItem(S3);
txtComboItemName.setCaretPosition((search_txt.getSelectedItem() + "").length());
search_txt.showPopup();
int i = search_txt.getItemCount();

if (i > search_txt.getMaximumRowCount()) {
search_txt.setMaximumRowCount(1000);
} else {
search_txt.setMaximumRowCount(i);
}
bbb = true;
} catch (Exception ex) {
ex.printStackTrace();
}

} else if (
evt.getKeyCode() == KeyEvent.VK_ENTER &&
bbb == true && evt.getKeyCode() == KeyEvent.VK_BACK_SPACE) {

boolean bIT = false;

String Sr123 = (String) search_txt.getSelectedItem();
try {
ResultSet Rst23 = DB.search("select '"+search_fields.getSelectedItem().toString()+"' from stud");

while (Rst23.next()) {
if (Sr123.equals(Rst23.getString(search_fields.getSelectedItem().toString()))) {
bIT = true;
break;
} else {
bIT = false;
}
}
bbb = false;
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
});
}

最佳答案

至少有一个问题是生成的查询将如下:

select 'COL' from stud where 'COL' like ..

什么时候应该看起来像

select COL from stud where COL like ..
-- or whatever is appropriate for the database (also note selecting into
-- a well-known column in this second case)
select [COL] as result from stud where [COL] like ..

也就是说,列名被错误地引用为字符串,并且未在 SQL 中用作标识符。

还有其他问题,SQL 注入(inject) - 因为提供给 LIKE 的应该由占位符绑定(bind),并且代码过于复杂,甚至可能更多。

<小时/>

考虑这些附加说明:

List<String> allowedNames = Arrays.asList<String>("FName", ..);

// Ensures the name is valid, or throws an Exception;
// it could also return a normalized name or a boolean, but an
// Exception is the quickest way to ensure "fail fast".
private void assertSearchableColumn(string colName) {
if (!allowedNames.contains(colName)) {
throw new RuntimeException("Invalid column");
}
}

// Then before a particular column is replaced in the SQL command, but there
// is no need to have function that merely sets the global variable.
String col = search_fields.getSelectedItem().toString();
assertSearchableColumn(col);

// Only replace columns, note that the columns are *not* quoted as strings
// in the resulting SQL, and that ? represents "a placeholder".
String sql = String.format("select %s from stud where %s like ?", col, col);

// And then bind the SQL with the appropriate value to use with LIKE.
// (I have no idea what "DB" is or how/if it supports placeholders, however..
// but if it does not already, it *should* support placeholders
// or else it is too easy for SQL Injection, accidental or otherwise.)

关于java - SQL Like 查询错误,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/24198200/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com