Prepared Statement IN 조항의 대안?
하기 위한 입니까?IN " " " 가 된 구java.sql.PreparedStatementSQL 주입 공격 보안 문제로 인해 여러 값에 대해 지원되지 않습니다., 하나.?플레이스 홀더는 값 목록이 아닌 하나의 값을 나타냅니다.
다음 SQL 문을 고려하십시오.
SELECT my_column FROM my_table where search_column IN (?)
「」를 사용합니다.preparedStatement.setString( 1, "'A', 'B', 'C'" );하지 않는 입니다.?★★★★★★★★★★★★★★★★★★.
어떤 회피책을 사용할 수 있습니까?
이용 가능한 다양한 옵션과 각각의 장단점에 대한 분석은 여기에서 확인할 수 있습니다.
권장되는 옵션은 다음과 같습니다.
- 하다.
SELECT my_column FROM my_table WHERE search_column = ?각 값에 대해 이 명령을 실행하고 클라이언트 측에서 UNION 결과를 생성합니다.준비된 문은 하나만 필요합니다.느리고 고통스럽다. - 하다.
SELECT my_column FROM my_table WHERE search_column IN (?,?,?)실행할 수 있습니다.size-of-IN-list마다 된 문이 필요합니다.르고명명 명명명다다 - 하다.
SELECT my_column FROM my_table WHERE search_column = ? ; SELECT my_column FROM my_table WHERE search_column = ? ; ...[어느 쪽인가]를 사용하다UNION ALL세미콜론 대신 --ed] IN 목록 크기당 준비된 문이 하나씩 필요합니다.멍청할 정도로 느리고, 엄밀히 말하면WHERE search_column IN (?,?,?)그래서 왜 그 블로거가 그것을 제안했는지 모르겠다. - 저장 프로시저를 사용하여 결과 세트를 구성합니다.
- 예를 들어 2, 10, 50개의 값을 사용하여 N개의 다른 사이즈 오브 IN 목록 쿼리를 준비합니다.의 다른 IN 를 6개의 IN 값으로 합니다.
SELECT my_column FROM my_table WHERE search_column IN (1,2,3,4,5,6,6,6,6,6)적절한 서버는 쿼리를 실행하기 전에 중복된 값을 최적화합니다.
이러한 옵션은 모두 이상적이지 않습니다.
하고 있고 JDBC4를 지원하는 를 사용하고 있는 입니다.x = ANY(y) , 을합니다.PreparedStatement.setArray여기에 기술한 바와 같이
수 있는 것 setArray명부에 기재되어 있다
SQL 문이 실행 시(예: 속성 파일에서) 로드되지만 변수 개수의 매개 변수가 필요할 수 있습니다.이 경우 먼저 쿼리를 정의합니다.
query=SELECT * FROM table t WHERE t.column IN (?)
다음으로 쿼리를 로드합니다.그런 다음 파라미터를 실행하기 전에 파라미터의 수를 결정합니다.파라미터 카운트가 확인되면 다음을 수행합니다.
sql = any( sql, count );
예를 들어 다음과 같습니다.
/**
* Converts a SQL statement containing exactly one IN clause to an IN clause
* using multiple comma-delimited parameters.
*
* @param sql The SQL statement string with one IN clause.
* @param params The number of parameters the SQL statement requires.
* @return The SQL statement with (?) replaced with multiple parameter
* placeholders.
*/
public static String any(String sql, final int params) {
// Create a comma-delimited list based on the number of parameters.
final StringBuilder sb = new StringBuilder(
String.join(", ", Collections.nCopies(possibleValue.size(), "?")));
// For more than 1 parameter, replace the single parameter with
// multiple parameter placeholders.
if (sb.length() > 1) {
sql = sql.replace("(?)", "(" + sb + ")");
}
// Return the modified comma-delimited list of parameters.
return sql;
}
4 되지 않는 에서는 이 JDBC 4의 .= ? ★★★★★★★★★★★★★★에IN (?)은 clause condition을 할 수 .any★★★★★★ 。
포스트그레용 솔루션SQL:
final PreparedStatement statement = connection.prepareStatement(
"SELECT my_column FROM my_table where search_column = ANY (?)"
);
final String[] values = getValues();
statement.setArray(1, connection.createArrayOf("text", values));
try (ResultSet rs = statement.executeQuery()) {
while(rs.next()) {
// do some...
}
}
또는
final PreparedStatement statement = connection.prepareStatement(
"SELECT my_column FROM my_table " +
"where search_column IN (SELECT * FROM unnest(?))"
);
final String[] values = getValues();
statement.setArray(1, connection.createArrayOf("text", values));
try (ResultSet rs = statement.executeQuery()) {
while(rs.next()) {
// do some...
}
}
AFAIK는 간단한 방법이 아닙니다.타깃이 스테이트먼트캐시 비율을 높게 유지하는 경우(즉, 모든 파라미터 카운트마다 스테이트먼트를 작성하는 것은 아닙니다), 다음을 실행할 수 있습니다.
몇 가지 매개 변수(예: 10)로 문장을 작성합니다.
...어디 AIN(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?...
모든 실행 매개 변수 바인딩
setString(1,"foo"), setString(2,"bar");
나머지를 NULL로 바인드합니다.
set Null(3, 유형).VARCHAR)... setNull(10, 타입).VARCHAR)
NULL은 일치하는 항목이 없으므로 SQL 계획 작성기에 의해 최적화됩니다.
목록을 DAO 함수로 전달하면 이 논리는 쉽게 자동화할 수 있습니다.
while( i < param.size() ) {
ps.setString(i+1,param.get(i));
i++;
}
while( i < MAX_PARAMS ) {
ps.setNull(i+1,Types.VARCHAR);
i++;
}
하시면 됩니다.Collections.nCopies 홀더에 String.join:
List<String> params = getParams();
String placeHolders = String.join(",", Collections.nCopies(params.size(), "?"));
String sql = "select * from your_table where some_column in (" + placeHolders + ")";
try ( Connection connection = getConnection();
PreparedStatement ps = connection.prepareStatement(sql)) {
int i = 1;
for (String param : params) {
ps.setString(i++, param);
}
/*
* Execute query/do stuff
*/
}
불쾌한 회피책은 네스트된 쿼리를 사용하는 것입니다.열이 있는 임시 테이블 MYVALUES를 만듭니다.값 목록을 MYVALUES 테이블에 삽입합니다.다음으로 실행한다.
select my_column from my_table where search_column in ( SELECT value FROM MYVALUES )
보기 흉하지만 값 목록이 매우 큰 경우 실행 가능한 대안입니다.
이 기법에는 최적화 도구에서 더 나은 쿼리 계획(페이지에 여러 값이 있는지 확인, 테이블은 값당 한 번만 사용할 수 있음 등)이 추가되어 데이터베이스가 준비된 문을 캐시하지 않으면 오버헤드를 줄일 수 있습니다."INSERT"는 일괄적으로 수행해야 하며, MYVALUES 테이블을 조정하여 잠금 또는 기타 오버헤드 보호를 최소화해야 할 수 있습니다.
in() 연산자의 제한은 모든 악의 근원입니다.
사소한 경우에서도 동작하며, 「준비된 스테이트먼트의 자동 생성」으로 확장할 수 있습니다만, 항상 한계가 있습니다.
- 변수 개수의 파라미터를 사용하여 문을 작성하는 경우 각 콜에서 SQL 해석 오버헤드가 발생합니다.
- 많은 플랫폼에서 in() 연산자의 파라미터 수는 제한되어 있습니다.
- 모든 플랫폼에서 SQL 텍스트의 총 크기가 제한되어 있기 때문에 2000개의 플레이스 홀더를 전송 할 수 없습니다.
- JDBC 드라이버에 제한이 있기 때문에 1000~10k의 바인드 변수를 다운할 수 없습니다.
in() 어프로치는 경우에 따라서는 충분하지만 로켓 프루프는 아닙니다.
로켓 프로프루프 솔루션은 임의의 수의 파라미터를 (예를 들어 파라미터의 클럭을 전달함으로써) 개별 콜에 전달한 후 뷰(또는 기타 방법)를 사용하여 SQL로 표현하고 where 기준으로 사용하는 것입니다.
brute-force의 변형은 http://tkyte.blogspot.hu/2006/06/varying-in-lists.html에 있습니다.
하지만 PL/SQL을 사용할 수 있다면 이 난장판은 깔끔해질 수 있습니다.
function getCustomers(in_customerIdList clob) return sys_refcursor is
begin
aux_in_list.parse(in_customerIdList);
open res for
select *
from customer c,
in_list v
where c.customer_id=v.token;
return res;
end;
그런 다음 매개 변수에서 임의의 수의 쉼표로 구분된 고객 ID를 전달할 수 있습니다.
- select의 SQL은 안정적이기 때문에 해석 지연은 발생하지 않습니다.
- 파이프라인 함수의 복잡성 없음 - 하나의 쿼리일 뿐입니다.
- SQL이 IN 연산자 대신 단순 조인(simple join)을 사용하고 있으며, 이는 매우 빠른 속도입니다.
- MySQL 또는 이와 유사한 단순한 데이터베이스 엔진을 제공하는 Oracle이기 때문에 일반 선택이나 DML로 데이터베이스를 타격하지 않는 것이 좋은 경험칙입니다.PL/SQL을 사용하면 애플리케이션 도메인 모델에서 스토리지 모델을 효과적으로 숨길 수 있습니다.
비결은 다음과 같습니다.
- 긴 문자열을 받아들여 db 세션이 접근할 수 있는 곳에 저장하는 콜이 필요합니다(예를 들어 simple package variable, dbms_variable).set_module)
- 이것을 행으로 해석할 수 있는 뷰가 필요합니다.
- 그러면 조회하는 ID가 포함된 뷰가 표시되므로 조회된 테이블에 간단히 조인하기만 하면 됩니다.
뷰는 다음과 같습니다.
create or replace view in_list
as
select
trim( substr (txt,
instr (txt, ',', 1, level ) + 1,
instr (txt, ',', 1, level+1)
- instr (txt, ',', 1, level) -1 ) ) as token
from (select ','||aux_in_list.getpayload||',' txt from dual)
connect by level <= length(aux_in_list.getpayload)-length(replace(aux_in_list.getpayload,',',''))+1
여기서 aux_in_list.getpayload는 원래 입력 문자열을 나타냅니다.
가능한 접근 방식은 pl/sql 어레이(Oracle에서만 지원)를 전달하는 것이지만, 이러한 어레이를 순수한 SQL에서는 사용할 수 없으므로 변환 단계가 항상 필요합니다.SQL에서는 변환을 할 수 없기 때문에 결국 모든 파라미터가 문자열에 포함된 clob을 전달하고 뷰에서 변환하는 것이 가장 효율적인 솔루션입니다.
제 어플리케이션으로 해결한 방법은 다음과 같습니다.String에 +를 사용하는 대신 String Builder를 사용하는 것이 이상적입니다.
String inParenthesis = "(?";
for(int i = 1;i < myList.size();i++) {
inParenthesis += ", ?";
}
inParenthesis += ")";
try(PreparedStatement statement = SQLite.connection.prepareStatement(
String.format("UPDATE table SET value='WINNER' WHERE startTime=? AND name=? AND traderIdx=? AND someValue IN %s", inParenthesis))) {
int x = 1;
statement.setLong(x++, race.startTime);
statement.setString(x++, race.name);
statement.setInt(x++, traderIdx);
for(String str : race.betFair.winners) {
statement.setString(x++, str);
}
int effected = statement.executeUpdate();
}
구체적인 숫자 대신 위의 x와 같은 변수를 사용하면 나중에 쿼리를 변경할 때 많은 도움이 됩니다.
한 번도 시도해 본 적은 없지만, .setArray()가 원하는 것을 할 수 있을까요?
업데이트: 분명히 없습니다.setArray는 java.sql에서만 동작하는 것 같습니다.이전 쿼리에서 검색한 ARARY 열 또는 ARARY 열이 있는 하위 쿼리에서 가져온 배열입니다.
회피책은 다음과 같습니다.
create or replace type split_tbl as table of varchar(32767);
/
create or replace function split
(
p_list varchar2,
p_del varchar2 := ','
) return split_tbl pipelined
is
l_idx pls_integer;
l_list varchar2(32767) := p_list;
l_value varchar2(32767);
begin
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
pipe row(substr(l_list,1,l_idx-1));
l_list := substr(l_list,l_idx+length(p_del));
else
pipe row(l_list);
exit;
end if;
end loop;
return;
end split;
/
이제 하나의 변수를 사용하여 테이블 내의 몇 가지 값을 얻을 수 있습니다.
select * from table(split('one,two,three'))
one
two
three
select * from TABLE1 where COL1 in (select * from table(split('value1,value2')))
value1 AAA
value2 BBB
따라서 준비된 진술은 다음과 같습니다.
"select * from TABLE where COL in (select * from table(split(?)))"
안부 전해요,
하비에르 이바네즈
(조작을 하여) 쿼리 수 .PreparedStatement 번이 있다?가 목록의 항목 수와 일치합니다.
있다면 은 사슬에 묶인 하는 데 한 만 더 OR 수가 .?이 문제를 어떻게 해결할 수 있는지 모르겠군요
다음 javadoc에서 설명한 대로 setArray 메서드를 사용할 수 있습니다.
PreparedStatement statement = connection.prepareStatement("Select * from emp where field in (?)");
Array array = statement.getConnection().createArrayOf("VARCHAR", new Object[]{"E1", "E2","E3"});
statement.setArray(1, array);
ResultSet rs = statement.executeQuery();
여기 Java에서 준비된 스테이트먼트를 작성하는 완전한 솔루션이 있습니다.
/*usage:
Util u = new Util(500); //500 items per bracket.
String sqlBefore = "select * from myTable where (";
List<Integer> values = new ArrayList<Integer>(Arrays.asList(1,2,4,5));
string sqlAfter = ") and foo = 'bar'";
PreparedStatement ps = u.prepareStatements(sqlBefore, values, sqlAfter, connection, "someId");
*/
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class Util {
private int numValuesInClause;
public Util(int numValuesInClause) {
super();
this.numValuesInClause = numValuesInClause;
}
public int getNumValuesInClause() {
return numValuesInClause;
}
public void setNumValuesInClause(int numValuesInClause) {
this.numValuesInClause = numValuesInClause;
}
/** Split a given list into a list of lists for the given size of numValuesInClause*/
public List<List<Integer>> splitList(
List<Integer> values) {
List<List<Integer>> newList = new ArrayList<List<Integer>>();
while (values.size() > numValuesInClause) {
List<Integer> sublist = values.subList(0,numValuesInClause);
List<Integer> values2 = values.subList(numValuesInClause, values.size());
values = values2;
newList.add( sublist);
}
newList.add(values);
return newList;
}
/**
* Generates a series of split out in clause statements.
* @param sqlBefore ""select * from dual where ("
* @param values [1,2,3,4,5,6,7,8,9,10]
* @param "sqlAfter ) and id = 5"
* @return "select * from dual where (id in (1,2,3) or id in (4,5,6) or id in (7,8,9) or id in (10)"
*/
public String genInClauseSql(String sqlBefore, List<Integer> values,
String sqlAfter, String identifier)
{
List<List<Integer>> newLists = splitList(values);
String stmt = sqlBefore;
/* now generate the in clause for each list */
int j = 0; /* keep track of list:newLists index */
for (List<Integer> list : newLists) {
stmt = stmt + identifier +" in (";
StringBuilder innerBuilder = new StringBuilder();
for (int i = 0; i < list.size(); i++) {
innerBuilder.append("?,");
}
String inClause = innerBuilder.deleteCharAt(
innerBuilder.length() - 1).toString();
stmt = stmt + inClause;
stmt = stmt + ")";
if (++j < newLists.size()) {
stmt = stmt + " OR ";
}
}
stmt = stmt + sqlAfter;
return stmt;
}
/**
* Method to convert your SQL and a list of ID into a safe prepared
* statements
*
* @throws SQLException
*/
public PreparedStatement prepareStatements(String sqlBefore,
ArrayList<Integer> values, String sqlAfter, Connection c, String identifier)
throws SQLException {
/* First split our potentially big list into lots of lists */
String stmt = genInClauseSql(sqlBefore, values, sqlAfter, identifier);
PreparedStatement ps = c.prepareStatement(stmt);
int i = 1;
for (int val : values)
{
ps.setInt(i++, val);
}
return ps;
}
}
봄에는 java.util을 통과할 수 있습니다.Named Parameter Jdbc Template에 목록합니다.인수 수에 따라 (?, ?, ?, ?, ?, ..., ?) 생성을 자동화합니다.
Oracle의 경우 이 블로그 게시에서는 oracle.sql 사용에 대해 설명합니다.어레이(Connection.createArrayOf는 Oracle과 함께 사용할 수 없습니다).이를 위해서는 SQL 문을 수정해야 합니다.
SELECT my_column FROM my_table where search_column IN (select COLUMN_VALUE from table(?))
Oracle 테이블 함수는 전달된 어레이를 테이블과 같은 값으로 변환합니다.IN★★★★★★ 。
instr 기능을 사용해 보시겠습니까?
select my_column from my_table where instr(?, ','||search_column||',') > 0
그리고나서
ps.setString(1, ",A,B,C,");
물론 이것은 좀 지저분한 해킹이지만 SQL 주입의 기회를 줄인다.어쨌든 오라클에서 작동합니다.
Sormula는 java.util을 제공할 수 있도록 하여 SQL IN 연산자를 지원합니다.매개 변수로서의 컬렉션 개체입니다.컬렉션의 각 요소에 대해 ?가 포함된 준비된 문을 만듭니다.예 4를 참조하십시오(예시의 SQL은 Sormula에 의해 작성되었지만 사용되지 않는 항목을 명확히 하기 위한 주석입니다).
PreparedStatement에서 쿼리 문자열을 생성하여 목록의 항목 수와 일치하는 ?을(를) 지정합니다.다음은 예를 제시하겠습니다.
public void myQuery(List<String> items, int other) {
...
String q4in = generateQsForIn(items.size());
String sql = "select * from stuff where foo in ( " + q4in + " ) and bar = ?";
PreparedStatement ps = connection.prepareStatement(sql);
int i = 1;
for (String item : items) {
ps.setString(i++, item);
}
ps.setInt(i++, other);
ResultSet rs = ps.executeQuery();
...
}
private String generateQsForIn(int numQs) {
String items = "";
for (int i = 0; i < numQs; i++) {
if (i != 0) items += ", ";
items += "?";
}
return items;
}
사용하는 대신
SELECT my_column FROM my_table where search_column IN (?)
SQL 문을 사용하다
select id, name from users where id in (?, ?, ?)
그리고.
preparedStatement.setString( 1, 'A');
preparedStatement.setString( 2,'B');
preparedStatement.setString( 3, 'C');
또는 저장 프로시저를 사용하는 것이 가장 좋습니다.sql 문은 컴파일되어 DataBase 서버에 저장됩니다.
준비된 진술과 관련하여 몇 가지 제한 사항을 발견했습니다.
- 준비된 문은 같은 세션(Postgres) 내에서만 캐시되므로 실제로 동작하는 것은 접속 풀링뿐입니다.
- @BalusC에서 제안하는 많은 준비된 문장으로 인해 캐시가 초과되어 이전에 캐시된 문장이 삭제될 수 있습니다.
- 쿼리를 최적화하고 인덱스를 사용해야 합니다.당연한 것처럼 들리지만, 예를 들어 상위 답변 중 하나에서 @Boris가 제안한 ANY(ARRAY...) 문장은 인덱스를 사용할 수 없으며 캐싱에도 쿼리가 느려집니다.
- 준비된 문은 쿼리 계획도 캐시하며 문에서 지정된 파라미터의 실제 값을 사용할 수 없습니다.
제안된 솔루션 중 쿼리 성능이 저하되지 않고 쿼리 수가 적은 솔루션을 선택합니다.이것은 @Don 링크에서 #4(몇 개의 쿼리를 배치하거나 @Vladimir Dyuzev가 제안한 '?' 마크에 대해 NULL 값을 지정합니다.
SetArray는 최적의 솔루션이지만 많은 오래된 드라이버에서는 사용할 수 없습니다.Java8에서는 다음 회피책을 사용할 수 있습니다.
String baseQuery ="SELECT my_column FROM my_table where search_column IN (%s)"
String markersString = inputArray.stream().map(e -> "?").collect(joining(","));
String sqlQuery = String.format(baseSQL, markersString);
//Now create Prepared Statement and use loop to Set entries
int index=1;
for (String input : inputArray) {
preparedStatement.setString(index++, input);
}
이 솔루션은 쿼리 문자열이 수동 반복으로 구축되는 추악한 루프 솔루션보다 우수합니다.
방금 포스트그레를 만들었는데이를 위한 SQL별 옵션입니다.이것은 약간 해킹이고, 그것 자체의 장단점과 제한이 있지만, 그것은 효과가 있는 것처럼 보이며 특정 개발 언어, 플랫폼 또는 PG 드라이버에 국한되지 않습니다.
물론 요령은 임의의 길이의 값 컬렉션을 단일 파라미터로 전달하고 db가 이를 여러 값으로 인식하도록 하는 것입니다.제가 작업하고 있는 솔루션은 컬렉션 내의 값에서 구분된 문자열을 생성하여 단일 파라미터로 전달하고 Postgre에 필요한 캐스팅과 함께 string_to_array()를 사용하는 것입니다.SQL을 사용하여 적절하게 활용합니다.
따라서 "foo", "blah" 및 "abc"를 검색하려면 "foo, blah, abc"와 같이 이들을 하나의 문자열로 연결할 수 있습니다.스트레이트 SQL은 다음과 같습니다.
select column from table
where search_column = any (string_to_array('foo,blah,abc', ',')::text[]);
명시적 캐스트를 결과값 배열(int, text, uuid 등)로 변경할 수 있습니다.또한 함수는 단일 문자열 값(또는 딜리미터를 커스터마이즈할 경우 2개)을 취하기 때문에 준비된 문에서 매개 변수로 전달할 수 있습니다.
select column from table
where search_column = any (string_to_array($1, ',')::text[]);
이는 다음과 같은 비교를 지원할 수 있을 정도로 유연합니다.
select column from table
where search_column like any (string_to_array('foo%,blah%,abc%', ',')::text[]);
물론 해킹이지만 작동하며 *ahem*개의 개별 파라미터를 사용하여 보안 및 (아마도) 성능상의 이점을 제공하는 미리 컴파일된 준비된 문장을 사용할 수 있습니다.권장되고 실제로 성능이 있습니까?당연히 문자열 구문 분석 및 쿼리가 실행되기도 전에 캐스팅이 진행되기 때문에 상황에 따라 달라집니다.3개, 5개, 수십개의 값을 보내실 예정이라면 괜찮으실 겁니다.몇 천?네, 아마 많이는 아닐 거예요.YMMV, 제한 및 제외가 적용되며 명시적 또는 묵시적 보증은 없습니다.
하지만 그것은 효과가 있다.
jOOQ, QueryDSL, 심지어 다음과 같이 발생할 수 있는 모든 엣지 케이스의 관리를 포함하여 동적 목록을 즉시 관리하는 Criteria Query와 같은 기성 쿼리 빌더를 사용할 것을 제안하는 사람은 아직 없는 것 같습니다.
- Oracle의 최대 1000개의 요소/초당
IN값의 ) list ('바인드 값의 수'에 관계없이) - 드라이버의 최대 바인드 수(이 답변에 기재되어 있음)에 도달하는 경우
- 너무 많은 SQL 문자열이 "하드 구문 분석"되어 실행 계획을 캐시할 수 없기 때문에 커서 캐시 경합 문제가 발생함(jOOQ 및 최근부터는 목록 패딩을 제공하여 휴지 상태 해결)
(면책자:jOOQ의 배후에 있는 회사에 근무하고 있습니다.)
완전성을 위해:값 집합이 너무 크지 않은 한, 다음과 같은 문구를 단순히 문자열로 구성할 수도 있습니다.
... WHERE tab.col = ? OR tab.col = ? OR tab.col = ?
그 후 루프로 setXX()를 사용하여 모든 값을 설정할 수 있습니다.이 방법은 터무니없어 보이지만, 많은 "대규모" 상용 시스템은 Oracle의 스테이트먼트에 대해 32KB(내 생각에는 그럴 것)와 같은 DB별 제한에 도달할 때까지 이러한 작업을 일상적으로 수행합니다.
물론 세트가 비정상적으로 커지거나, 커졌을 경우에 에러 트래핑을 실시하지 않도록 할 필요가 있습니다.
아담의 생각대로.준비된 문장으로 my_table에서 my_column을 선택합니다.여기서 search_column in (#) string x를 만들고 값 목록에 따라 "?,?"의 숫자로 입력합니다.그러면 새로운 String x an populate에 대한 쿼리의 #를 변경하기만 하면 됩니다.
Prepared Statement의 IN 조항에 사용할 수 있는 다른 방법이 있습니다.
- 단일 쿼리 사용 - 퍼포먼스가 가장 느리고 리소스를 가장 많이 사용
- Stored Procedure 사용 - 가장 빠르지만 데이터베이스 고유
- Prepared Statement에 대한 동적 쿼리 생성 - 성능이 우수하지만 캐싱의 이점을 얻지 못하고 매번 Prepared Statement가 다시 컴파일됩니다.
PreparedStatement 쿼리에서 NULL 사용 - 최적의 성능. IN 절 인수의 제한을 알고 있으면 매우 효과적입니다.제한이 없는 경우 쿼리를 일괄적으로 실행할 수 있습니다.샘플 코드 스니펫은 다음과 같습니다.
int i = 1; for(; i <=ids.length; i++){ ps.setInt(i, ids[i-1]); } //set null for remaining ones for(; i<=PARAM_SIZE;i++){ ps.setNull(i, java.sql.Types.INTEGER); }
이러한 대체 접근법에 대한 자세한 내용은 여기를 참조하십시오.
상황에 따라 regexp가 도움이 될 수 있습니다.다음은 Oracle에서 확인한 예시로, 작동됩니다.
select * from my_table where REGEXP_LIKE (search_column, 'value1|value2')
단, 다음과 같은 단점이 있습니다.
- 적용된 열은 적어도 암묵적으로 varchar/char로 변환해야 합니다.
- 특수 문자에 주의해야 합니다.
- 퍼포먼스가 저하될 수 있습니다.내 경우 IN 버전은 인덱스와 범위 스캔을 사용하고, REGEXP 버전은 풀 스캔을 실시합니다.
여러 포럼에서 다양한 솔루션을 검토했지만 좋은 솔루션을 찾지 못한 후, 제가 생각해낸 아래의 해킹이 가장 쉽게 따라하고 코드화할 수 있다고 느꼈습니다.
예:'에 전달할 파라미터가 여러 개 있다고 가정합니다.IN' 조항.'에 더미 스트링을 넣기만 하면 됩니다.IN' 절, 예를 들어 "PARAM"은 이 더미 문자열 대신 오는 매개 변수 목록을 나타냅니다.
select * from TABLE_A where ATTR IN (PARAM);
모든 파라미터를 Java 코드의 단일 String 변수로 수집할 수 있습니다.이것은 다음과 같이 실행할 수 있습니다.
String param1 = "X";
String param2 = "Y";
String param1 = param1.append(",").append(param2);
콤마로 구분된 모든 파라미터를 단일 String 변수 'param1'에 추가할 수 있습니다.
모든 파라미터를 1개의 String으로 수집한 후 쿼리의 더미텍스트(이 경우 "PARAM")를 파라미터 String(예: param1)으로 대체할 수 있습니다.필요한 것은 다음과 같습니다.
String query = query.replaceFirst("PARAM",param1); where we have the value of query as
query = "select * from TABLE_A where ATTR IN (PARAM)";
이제 execute를 사용하여 쿼리를 실행할 수 있습니다.Query() 메서드.쿼리에 "PARAM"이라는 단어가 없는지 확인합니다.이러한 단어가 쿼리에 들어가지 않도록 하기 위해 "PARAM" 단어 대신 특수 문자와 알파벳을 조합하여 사용할 수 있습니다.해결책을 찾았길 바라.
주의: 준비된 쿼리는 아니지만 코드가 원하는 작업을 수행합니다.
단지 완성도를 위해, 그리고 다른 누군가가 그것을 제안하지 않았기 때문에:
위의 복잡한 제안 중 하나를 구현하기 전에 실제로 SQL 주입이 시나리오에서 문제인지 여부를 고려하십시오.
대부분의 경우 IN(...)에 제공되는 값은 주입이 불가능하도록 생성된 ID 목록입니다.(예: 이전 결과 some_id from some_table with some_condition).
이 경우 이 값을 연결하기만 하면 서비스나 준비된 문을 사용하지 않거나 이 쿼리의 다른 파라미터에 사용할 수 있습니다.
query="select f1,f2 from t1 where f3=? and f2 in (" + sListOfIds + ");";
Prepared Statement는 SQL IN 절을 처리하는 좋은 방법을 제공하지 않습니다.http://www.javaranch.com/journal/200510/Journal200510.jsp#a2에 따르면 "SQL 문의 일부가 되는 것을 대체할 수 없습니다.SQL 자체를 변경할 수 있는 경우 드라이버가 문을 미리 컴파일할 수 없기 때문에 이것이 필요합니다.또한 SQL 주입 공격을 방지하는 좋은 부작용도 있습니다."결국 다음과 같은 방법을 사용했습니다.
String query = "SELECT my_column FROM my_table where search_column IN ($searchColumns)";
query = query.replace("$searchColumns", "'A', 'B', 'C'");
Statement stmt = connection.createStatement();
boolean hasResults = stmt.execute(query);
do {
if (hasResults)
return stmt.getResultSet();
hasResults = stmt.getMoreResults();
} while (hasResults || stmt.getUpdateCount() != -1);
네, 그 전에 어디서 어떻게 이 작업을 수행했는지 정확히 기억할 수 없었기 때문에 스택 오버플로를 찾아 빠르게 답을 찾았습니다.할 수 없어서 놀랐어요.
오래 전에 IN 문제를 해결할 수 있었던 방법은 다음과 같습니다.
여기서 myColumn in(regexp_substr(:myList(':myList', [^,]+', 1, level))은 null이 아닙니다.
myList 매개 변수를 쉼표로 구분된 문자열로 설정합니다. A, B, C, D...
참고: 파라미터를 두 번 설정해야 합니다!
이것은 나에게 효과가 있었다(psuedocode):
public class SqlHelper
{
public static final ArrayList<String>platformList = new ArrayList<>(Arrays.asList("iOS","Android","Windows","Mac"));
public static final String testQuery = "select * from devices where platform_nm in (:PLATFORM_NAME)";
}
바인딩을 지정합니다.
public class Test extends NamedParameterJdbcDaoSupport
public List<SampleModelClass> runQuery()
{
//define rowMapper to insert in object of SampleClass
final Map<String,Object> map = new HashMap<>();
map.put("PLATFORM_LIST",DeviceDataSyncQueryConstants.platformList);
return getNamedParameterJdbcTemplate().query(SqlHelper.testQuery, map, rowMapper)
}
언급URL : https://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives
'programing' 카테고리의 다른 글
| jQuery 함수(새로운 jQuery 메서드 또는 플러그인)를 만드는 방법 (0) | 2022.11.27 |
|---|---|
| Regex 플러스 대 스타의 차이? (0) | 2022.11.27 |
| MariaDB의 인덱스 키 크기 제한은 얼마입니까? (0) | 2022.11.27 |
| Numpy: 2D 어레이에서 행 집합을 무작위로 가져옵니다. (0) | 2022.11.27 |
| 이클립스를 위한 최고의 GUI 디자이너? (0) | 2022.11.26 |