PreparedStatement IN 절 대안?
SQL 주입 공격 보안 문제로 인해 여러 값에서 지원되지 않는의 IN인스턴스와 함께 SQL 절 을 사용하는 가장 좋은 해결 방법은 다음 과 java.sql.PreparedStatement같습니다. 하나의 ?자리 표시자는 값 목록이 아닌 하나의 값을 나타냅니다.
다음 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 (?,?,?)하고 실행하십시오. 목록 크기 당 하나의 준비된 명령문이 필요합니다. 빠르고 명백합니다. - 준비
SELECT my_column FROM my_table WHERE search_column = ? ; SELECT my_column FROM my_table WHERE search_column = ? ; ...하고 실행하십시오. [또는UNION ALL그 세미콜론 대신 사용 하십시오. --ed] 목록 크기 당 하나의 준비된 명령문이 필요합니다. 엄청나게 느리고, 엄청나게 나쁘기WHERE search_column IN (?,?,?)때문에 블로거가 왜 제안했는지 모르겠습니다. - 스토어드 프로 시저를 사용하여 결과 세트를 구성하십시오.
- N 개의 다른 크기 목록 쿼리를 준비하십시오. 2, 10 및 50 값으로 말하십시오. 6 개의 다른 값으로 IN 목록을 검색하려면 다음과 같이 size-10 쿼리를 채 웁니다
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그러나 IN 목록으로 작업 하는 방법은 없습니다 .
때때로 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(
new String(new char[params]).replace("\0", "?,")
);
// Remove trailing comma.
sb.setLength(Math.max(sb.length() - 1, 0));
// 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;
}
JDBC 4 스펙을 통한 배열 전달이 지원되지 않는 특정 데이터베이스의 경우,이 메소드는 느린 = ?것을 더 빠른 IN (?)절 조건 으로 변환하는 것을 용이하게 할 수 있으며, 그런 다음 any메소드 를 호출하여 확장 할 수 있습니다 .
PostgreSQL 솔루션 :
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));
final ResultSet rs = statement.executeQuery();
try {
while(rs.next()) {
// do some...
}
} finally {
rs.close();
}
또는
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));
final ResultSet rs = statement.executeQuery();
try {
while(rs.next()) {
// do some...
}
} finally {
rs.close();
}
간단한 방법은 없습니다. 대상이 명령문 캐시 비율을 높게 유지하는 것 (즉, 모든 매개 변수 수마다 명령문을 작성하지 않는 것) 인 경우 다음을 수행 할 수 있습니다.
몇 가지 (예 : 10) 매개 변수로 명령문을 작성하십시오.
... 어디에서 (?,?,?,?,?,?,?,?,?,?) ...
모든 실제 매개 변수 바인드
setString (1, "foo"); setString (2, "bar");
나머지를 NULL로 바인드
setNull (3, Types.VARCHAR) ... setNull (10, Types.VARCHAR)
NULL은 절대로 일치하지 않으므로 SQL 계획 빌더에 의해 최적화됩니다.
List를 DAO 함수에 전달하면 로직을 쉽게 자동화 할 수 있습니다.
while( i < param.size() ) {
ps.setString(i+1,param.get(i));
i++;
}
while( i < MAX_PARAMS ) {
ps.setNull(i+1,Types.VARCHAR);
i++;
}
불쾌한 해결 방법이지만 확실하게 가능한 것은 중첩 쿼리를 사용하는 것입니다. 열이있는 임시 테이블 MYVALUES를 작성하십시오. MYVALUES 테이블에 값 목록을 삽입하십시오. 그런 다음 실행
select my_column from my_table where search_column in ( SELECT value FROM MYVALUES )
추악하지만 값 목록이 매우 큰 경우 실행 가능한 대안입니다.
이 기술은 데이터베이스가 준비된 명령문을 캐시하지 않으면 옵티마이 저의 잠재적으로 더 나은 쿼리 계획 (여러 값에 대한 페이지 확인, 값당 한 번에 한 번만 테이블 스캔 등)의 오버 헤드를 줄일 수있는 이점이 있습니다. "INSERTS"를 일괄 처리해야하며 잠금 또는 최소 오버 헤드 방지를 최소화하기 위해 MYVALUES 테이블을 조정해야 할 수도 있습니다.
in () 연산자의 한계는 모든 악의 근원입니다.
사소한 경우에는 작동하며 "준비된 명령문의 자동 생성"으로 확장 할 수 있지만 항상 한계가 있습니다.
- 가변 개수의 매개 변수로 명령문을 작성하면 각 호출에서 SQL 구문 분석 오버 헤드가 발생합니다.
- 많은 플랫폼에서 in () 연산자의 매개 변수 수는 제한되어 있습니다.
- 모든 플랫폼에서 총 SQL 텍스트 크기가 제한되어 in 매개 변수에 대해 2000 자리 표시자를 보낼 수 없습니다.
- JDBC 드라이버에 한계가 있으므로 1000-10k의 바인드 변수를 보내는 것은 불가능합니다.
in () 접근 방식은 어떤 경우에는 충분하지만 로켓 증거는 아닙니다. :)
로켓 방지 솔루션은 임의의 수의 매개 변수를 별도의 호출 (예 : 매개 변수의 clob를 전달하여)으로 전달 한 다음 SQL로 표현하고 어디에서나 사용할 수있는보기 (또는 다른 방법) 기준.
무차별 대입 변형은 여기 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 연산자 대신 간단한 조인을 사용하고 있습니다.
- 결국, MySQL이나 유사한 간단한 데이터베이스 엔진보다 수년 이상을 제공하는 Oracle이기 때문에 일반 선택 또는 DML로 데이터베이스에 충돌 하지 않는 것이 좋습니다 . PL / SQL을 사용하면 효과적인 방식으로 애플리케이션 도메인 모델에서 스토리지 모델을 숨길 수 있습니다.
트릭은 다음과 같습니다.
- 긴 문자열을 받아들이고 db 세션이 액세스 할 수있는 위치 (예 : 간단한 패키지 변수 또는 dbms_session.set_context)를 저장하는 호출이 필요합니다.
- 그런 다음 행을 파싱 할 수있는 뷰가 필요합니다.
- 그런 다음 쿼리하는 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를 전달하고 뷰를 사용하여 변환하는 것이 가장 효율적인 솔루션입니다.
내 응용 프로그램에서 해결하는 방법은 다음과 같습니다. 이상적으로는 + for Strings 대신 StringBuilder를 사용해야합니다.
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는 이전 쿼리에서 검색 한 ARRAY 열 또는 ARRAY 열이있는 하위 쿼리에서 가져온 java.sql.Array에서만 작동하는 것 같습니다.
내 해결 방법은 다음과 같습니다.
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();
instr 기능을 사용해보십시오?
select my_column from my_table where instr(?, ','||search_column||',') > 0
그때
ps.setString(1, ",A,B,C,");
분명히 이것은 약간의 더러운 해킹이지만 SQL 삽입의 기회를 줄입니다. 어쨌든 오라클에서 작동합니다.
Sormula 는 java.util.Collection 오브젝트를 매개 변수로 제공 할 수 있도록하여 SQL IN 연산자를 지원합니다. ?로 준비된 명령문을 작성합니다. 각 요소에 대해 컬렉션입니다. 예 4를 참조하십시오 ( 예 에서 SQL은 Sormula에서 작성되었지만 사용하지 않는 것을 설명하기위한 주석입니다).
사용하는 대신
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 문이 데이터베이스 서버에 컴파일되어 저장되므로 저장 프로 시저를 사용하는 것이 가장 좋습니다.
나는 준비된 진술과 관련된 여러 가지 한계를 겪었습니다.
- 준비된 명령문은 동일한 세션 (Postgres) 내에서만 캐시되므로 연결 풀링에서만 작동합니다.
- @BalusC가 제안한 다양한 준비된 명령문은 캐시가 과도하게 채워져 이전에 캐시 된 명령문이 삭제 될 수 있습니다.
- 쿼리는 최적화되고 인덱스를 사용해야합니다. 그러나 명백하게 들립니다. 예를 들어 @Boris가 제안한 ANY (ARRAY ...) 문은 최상위 답변 중 하나에서 인덱스를 사용할 수 없으며 캐싱에도 불구하고 쿼리 속도가 느려집니다
- 준비된 명령문은 쿼리 계획도 캐시하며 명령문에 지정된 매개 변수의 실제 값은 사용할 수 없습니다.
제안 된 솔루션 중에서 쿼리 성능을 저하시키지 않고 쿼리 수를 줄이는 솔루션을 선택합니다. @Don 링크에서 # 4 (몇 개의 쿼리를 배치)이거나 불필요한 '?'에 NULL 값을 지정합니다. @Vladimir Dyuzhev가 제안한 마크
다음은 준비된 명령문을 작성하기위한 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;
}
}
Spring은 java.util.Lists를 NamedParameterJdbcTemplate에 전달 하여 인수 수에 따라 (?,?,?, ...,?) 생성을 자동화합니다.
Oracle의 경우이 블로그 게시물 에서는 oracle.sql.ARRAY 사용에 대해 설명합니다 (Connection.createArrayOf는 Oracle에서 작동하지 않음). 이를 위해 SQL 문을 수정해야합니다.
SELECT my_column FROM my_table where search_column IN (select COLUMN_VALUE from table(?))
Oracle 테이블 함수 의 값과 같은 이용 가능한 테이블로 전달 된 배열을 변환 IN문.
완전성을 위해 : 값 집합이 너무 크지 않으면 다음과 같은 문장을 간단히 문자열로 구성 할 수 있습니다
... WHERE tab.col = ? OR tab.col = ? OR tab.col = ?
그런 다음 Preparing ()에 전달한 다음 루프에서 setXXX ()를 사용하여 모든 값을 설정할 수 있습니다. 이것은 유쾌 해 보이지만 많은 "큰"상용 시스템은 Oracle의 문장에 대해 32KB (예 : 생각)와 같은 DB 관련 제한에 도달 할 때까지 이러한 종류의 작업을 일상적으로 수행합니다.
물론 세트가 부당하게 커지지 않도록하거나 세트가있는 경우 오류 트래핑을 수행하지 않아야합니다.
아담의 생각을 따르십시오. 준비된 명령문을 my_table에서 select my_column으로 선택하십시오. 여기서 search_column in (#) String x를 작성하고 여러 개의 "?,?,?"로 채 웁니다. 값 목록에 따라 새 문자열 x에 대한 쿼리에서 #을 변경하십시오.
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;
}
PreparedStatement에서 IN 절에 사용할 수있는 다른 대체 방법이 있습니다.
- 단일 쿼리 사용-성능 저하 및 리소스 집약
- StoredProcedure 사용-가장 빠르지 만 데이터베이스 별
- PreparedStatement에 대한 동적 쿼리 작성-성능은 우수하지만 캐싱의 이점을 얻지 못하고 PreparedStatement는 매번 다시 컴파일됩니다.
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 param1 = "X";
String param2 = "Y";
String param1 = param1.append(",").append(param2);
이 경우 쉼표로 구분 된 모든 매개 변수를 단일 문자열 변수 'param1'에 추가 할 수 있습니다.
모든 매개 변수를 단일 문자열로 수집 한 후에는 쿼리의 더미 텍스트 (이 경우 "PARAM")를 매개 변수 문자열 (param1)로 바꿀 수 있습니다. 해야 할 일은 다음과 같습니다.
String query = query.replaceFirst("PARAM",param1); where we have the value of query as
query = "select * from TABLE_A where ATTR IN (PARAM)";
이제 executeQuery () 메소드를 사용하여 쿼리를 실행할 수 있습니다. 검색어에 "PARAM"이라는 단어가 없는지 확인하십시오. "PARAM"이라는 단어 대신 특수 문자와 알파벳의 조합을 사용하여 해당 단어가 쿼리에 올 가능성이 없도록 할 수 있습니다. 당신이 해결책을 얻었기를 바랍니다.
참고 : 이것은 준비된 쿼리가 아니지만 코드에서 원하는 작업을 수행합니다.
완전성을 위해 그리고 다른 누군가가 그것을 보지 못했기 때문에 :
위의 복잡한 제안을 구현하기 전에 SQL 주입이 실제로 시나리오에 문제가 있는지 고려하십시오.
대부분의 경우 IN (...)에 제공된 값은 주입이 불가능하다는 것을 확신 할 수있는 방식으로 생성 된 ID 목록입니다 (예 : some_table에서 이전 select some_id의 결과) some_condition.)
이 경우이 값을 연결하고 서비스 또는 준비된 명령문을 사용하지 않거나이 조회의 다른 매개 변수에 사용하십시오.
query="select f1,f2 from t1 where f3=? and f2 in (" + sListOfIds + ");";
PreparedStatement는 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);
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);
}
이 솔루션은 수동 반복으로 쿼리 문자열을 작성하는 다른 못생긴 while 루프 솔루션보다 낫습니다.
당신이 사용할 수있는 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
*/
}
방금 PostgreSQL 관련 옵션을 해결했습니다. 그것은 약간의 해킹이며 자체 장단점과 한계가 있지만 작동하는 것으로 보이며 특정 개발 언어, 플랫폼 또는 PG 드라이버로 제한되지 않습니다.
물론, 임의의 길이의 값 모음을 단일 매개 변수로 전달하고 db가이를 여러 값으로 인식하도록하는 방법을 찾는 것이 중요합니다. 내가 작업 한 솔루션은 컬렉션의 값에서 구분 된 문자열을 구성하고 해당 문자열을 단일 매개 변수로 전달한 다음 string_to_array ()를 PostgreSQL의 필수 캐스팅과 함께 사용하여 올바르게 사용하는 것입니다.
따라서 "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 등 결과 값 배열을 원하는 것으로 명시 캐스트를 분명히 변경합니다. 함수가 단일 문자열 값을 취하기 때문에 (또는 구분 기호를 사용자 정의하려는 경우 두 가지로 가정합니다) 또한), 준비된 명령문에서 매개 변수로 전달할 수 있습니다.
select column from table
where search_column = any (string_to_array($1, ',')::text[]);
이것은 LIKE 비교와 같은 것을 지원하기에 충분히 유연합니다.
select column from table
where search_column like any (string_to_array('foo%,blah%,abc%', ',')::text[]);
다시 말하지만, 그것은 해킹이지만 의심의 여지없이 작동하며 보안 및 성능상의 이점과 함께 * ahem * 개별 매개 변수를 사용하는 사전 컴파일 된 준비된 명령문을 계속 사용할 수 있습니다 . 바람직하고 실제로 성능이 있습니까? 당연히 문자열 구문 분석이 가능하고 쿼리가 실행되기 전에 캐스팅이 진행되기 때문에 상황에 따라 다릅니다. 3, 5, 수십 개의 값을 보내려면 아마 괜찮을 것입니다. 몇 천? 예, 그렇게 많지 않을 수도 있습니다. YMMV, 제한 및 제외가 적용되며, 명시 적 또는 묵시적 보증은 없습니다.
그러나 작동합니다.
내 해결 방법 (자바 스크립트)
var s1 = " SELECT "
+ "FROM table t "
+ " where t.field in ";
var s3 = '(';
for(var i =0;i<searchTerms.length;i++)
{
if(i+1 == searchTerms.length)
{
s3 = s3+'?)';
}
else
{
s3 = s3+'?, ' ;
}
}
var query = s1+s3;
var pstmt = connection.prepareStatement(query);
for(var i =0;i<searchTerms.length;i++)
{
pstmt.setString(i+1, searchTerms[i]);
}
SearchTerms 입력 / 키 / 필드 등을 포함하는 배열입니다
참고 URL : https://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives
'Programming' 카테고리의 다른 글
| Windows의 자산 index.android.bundle에서 스크립트를로드 할 수 없습니다 (0) | 2020.03.06 |
|---|---|
| 무결성 및 crossorigin 속성은 무엇입니까? (0) | 2020.03.06 |
| Linux 커널에서 가능성이 높거나 가능성이 낮은 매크로는 어떻게 작동하며 그 이점은 무엇입니까? (0) | 2020.03.06 |
| Java 패키지 이름에서 단어 구분 기호에 대한 규칙은 무엇입니까? (0) | 2020.03.06 |
| git의“rebase --preserve-merges”는 정확히 무엇을하며 왜 그런가? (0) | 2020.03.06 |