준비된 명세서

Prepared statement

데이터베이스 관리 시스템(DBMS)에서 준비된 문 또는 매개 변수화된 문은 SQL 코드를 사전 컴파일하여 데이터와 분리하는 데 사용되는 기능입니다.작성된 진술서의 장점은 다음과 같습니다.[1]

  • 효율성은 재평가 없이 반복적으로 사용할 수 있기 때문입니다.
  • 보안, SQL 주입 공격 감소 또는 제거

준비된 문은 각 실행 중에 상수 값이 대체되는 미리 컴파일된 템플릿의 형식을 취하며 일반적으로 INSERT, SELECT 또는 UPDATE 등의 SQL DML 문을 사용합니다.

준비된 스테이트먼트의 일반적인 워크플로우는 다음과 같습니다.

  1. 준비:응용 프로그램은 스테이트먼트 템플릿을 생성하여 DBMS로 전송합니다.특정 값은 지정되지 않은 상태로 남습니다.파라미터, 플레이스 홀더 또는 바인드 변수(아래 "?"로 표시됨):
    INSERT INTO products (name, price) VALUES (?, ?);
  2. 컴파일:DBMS는 스테이트먼트템플릿을 컴파일(파싱, 최적화 및 변환)하여 실행하지 않고 결과를 저장합니다.
  3. 실행:응용 프로그램은 스테이트먼트템플릿의 파라미터 값을 제공하고(또는 바인드), DBMS는 스테이트먼트를 실행합니다(결과를 반환할 가능성이 있습니다).응용 프로그램은 DBMS에 다른 값을 사용하여 여러 번 문을 실행하도록 요청할 수 있습니다.위의 예에서 응용 프로그램은 첫 번째 매개변수에 대해 "bike" 값을 제공하고 두 번째 매개변수에 대해 "10900" 값을 제공한 다음 나중에 "shoes" 및 "7400" 값을 제공할 수 있습니다.

준비된 스테이트먼트의 대안은 코드와 데이터를 조합한 방법으로 애플리케이션 소스 코드에서 SQL을 직접 호출하는 것입니다.위의 예와 직접 동등한 것은 다음과 같습니다.

INSERT INTO products (name, price) VALUES ("bike", "10900");

최적의 플랜은 파라미터의 특정 값에 따라 달라질 수 있으며 최적의 플랜은 시간이 [2]지남에 따라 변경될 수 있습니다.

한편 쿼리를 한 번만 실행하면 서버 [3]측 준비 스테이트먼트가 느려질 수 있습니다.이는 서버에 대한 추가 라운드 트립이 발생하기 때문입니다.구현 제한으로 인해 성능 저하가 발생할 수 있습니다. 예를 들어 MySQL 일부 버전에서는 준비된 [4]쿼리의 결과가 캐시되지 않았습니다.저장 프로시저도 나중에 실행할 수 있도록 미리 컴파일되어 서버에 저장됩니다.스토어드 프로시저와는 달리 준비된 스테이트먼트는 보통 절차 언어로 작성되지 않으며, 선언형 데이터베이스 쿼리 언어에 의존하여 변수나 수정, 제어 흐름 구조를 사용할 수 없습니다.심플함과 클라이언트측의 에뮬레이션으로 인해, 벤더간에 준비된 스테이트먼트를 보다 간단하게 휴대할 수 있습니다.

소프트웨어 지원

SQLite,[5] MySQL,[6] Oracle,[7] IBM DB2,[8] Microsoft SQL[9] Server 및 Postgre를 포함한 주요 DBMSSQL[10] 지원 준비문.준비된 문은 일반적으로 SQL 주입으로부터 효율성과 보호를 위해 비 SQL 이진 프로토콜을 통해 실행되지만 MySQL 준비된 문과 같은 일부 DBMS에서는 SQL 구문을 사용하여 디버깅할 [11]수도 있습니다.

Java의 JDBC,[12] Perl의 DBI,[13] PHP의 PDO[1] 및 Python의 [14]DB-API를 포함하여 많은 프로그래밍 언어가 표준 라이브러리에서 준비된 문을 지원하며 클라이언트 측에서 이를 에뮬레이트합니다.서버로의 라운드 트립 횟수를 줄임으로써 1회만 실행되는 쿼리의 경우 클라이언트 측 에뮬레이션을 고속화할 수 있지만, 일반적으로 여러 번 실행되는 쿼리의 경우 속도가 느려집니다.SQL 주입 공격에도 효과적으로 저항합니다.

리터럴을 디세블로 하면 많은 유형의 SQL 주입 공격을 제거할 수 있으며, 실제로 준비된 스테이트먼트를 사용해야 합니다.2007년 현재 [15]이 기능을 지원하는 것은 H2뿐입니다.

예

Java JDBC

다음 예제에서는 Java 및 JDBC를 사용합니다.

수입품 com.bc.jdbc.jdbc2.옵션Mysql 데이터 원본; 수입품 java.sql.연결; 수입품 java.sql.드라이버 매니저; 수입품 java.sql.준비 스테이트먼트; 수입품 java.sql.결과 세트; 수입품 java.sql.SQLException; 수입품 java.sql.진술;  일반의 학급 주된 {      일반의 정적인 무효 주된(스트링[] args) 던지다 SQLException {         Mysql 데이터 원본 ds = 신규 Mysql 데이터 원본();         ds.setDatabaseName("실패");         ds.set User (set User(사용자 설정)("루트");          해라 (연결 접속하다 = ds.get Connection(get Connection)()) {             해라 (진술 스탑 = 접속하다.create 스테이트먼트()) {                 스탑.execute Update(업데이트 실행)("제품이 없는 경우 테이블 생성(VARCHAR(40), 가격 INT)");             }              해라 (준비 스테이트먼트 스탑 = 접속하다.준비문(제품의 가치(?, ?)에 삽입)) {                 스탑.setString(1, '자전거");                 스탑.setInt(2, 10900);                 스탑.execute Update(업데이트 실행)();                 스탑.setString(1, "실패");                 스탑.setInt(2, 7400);                 스탑.execute Update(업데이트 실행)();                 스탑.setString(1, "전화");                 스탑.setInt(2, 29500);                 스탑.execute Update(업데이트 실행)();             }              해라 (준비 스테이트먼트 스탑 = 접속하다.준비문("SELECT * FROM 제품 이름 =?")) {                 스탑.setString(1, "실패");                 결과 세트 rs = 스탑.executeQuery();                 rs.다음 분.();                 시스템..나가..인쇄(rs.취득하다(2));             }         }     } }

자바PreparedStatement에는 「설정자」(setInt(int), setString(String), setDouble(double),모든 주요 빌트인 데이터 타입에 대응합니다.

PHP PDO

다음 예제에서는 PHP 및 PDO를 사용합니다.

<?개요  해라 {     // 비밀번호가 "root"인 "mysql"인 데이터베이스에 연결합니다.     $connection(접속) = 신규 PDO('filename:dbname=filename', 'root');      // 연결에서 요청을 실행합니다.이것에 의해     // "이름"과 "가격"의 두 열이 있는 테이블 "제품"     $connection(접속)->이그제큐티브('제품(이름 VARCHAR(40), 가격 INT)'이 존재하지 않는 경우 표 생성');      // 테이블에 여러 제품을 삽입하기 위한 쿼리를 준비합니다.     $statement($statement) = $connection(접속)->준비한다.('제품의 가치에 삽입(?, ?)');     $제품  = [         ['자전거, 10900],         ['실패', 7400],         ['전화', 29500],     ];      // "products" 어레이 내의 제품을 반복하여     // 각 제품에 대해 준비된 명세서를 실행합니다.     앞지르다 ($제품 ~하듯이 $제품) {         $statement($statement)->실행하다($제품);     }      // 명명된 매개 변수를 사용하여 새 문을 준비합니다.     $statement($statement) = $connection(접속)->준비한다.('SELECT * FROM 제품 WHERE 이름 = :name');     $statement($statement)->실행하다([         ': 이름' => '실패',     ]);      // 어레이 파괴를 사용하여 제품 이름과 가격 할당     // 대응하는 변수까지     [ $제품, 가격 ] = $statement($statement)->가지고 오다();      // 사용자에게 결과 표시     메아리치다 "제품 가격{$제품}이\${가격}.";      // 'fetch'를 다시 사용할 수 있도록 커서를 닫습니다.     $statement($statement)->커서 닫기(); } 또 만나 (\예외 e달러) {     메아리치다 '오류가 발생했습니다.' . e달러->get Message(); }

Perl DBI

다음 예제에서는 Perl 및 DBI를 사용합니다.

#!/usr/bin/syslog -w 사용하다 엄격한.; 사용하다 DBI;  나의 ($db_name, $db_user, $db_password) = ('my_my_my', '모이', '패스W0rD'); 나의 dbh달러 = DBI->연결하다("DBI:mysql:database=$db_name", $db_user, $db_password,     { Raise Error(레이즈 에러 => 1, 자동 커밋 => 1})     또는 죽어버려 "ERROR (메인:DBI->connect) 데이터베이스 $db_name 연결 중: " .         $DBI::에러스트 . "\n";  dbh달러->하니('제품(이름 VARCHAR(40), 가격 INT)'이 존재하지 않는 경우 표 생성');  나의 $140 = dbh달러->준비한다.('제품의 가치에 삽입(?, ?)'); $140->실행하다(@$_) 앞지르다 ['자전거, 10900], ['실패', 7400], ['전화', 29500];  $140 = dbh달러->준비한다.("SELECT * FROM 제품 이름 =?"); $140->실행하다('실패'); 인쇄물 "$_[1]\n" 앞지르다 $140->fetchrow_arrayref; $140->끝내라.;  dbh달러->절단하다;

C# ADO.NET

이 예에서는, C# 와 ADO 를 사용하고 있습니다.네트워크:

사용. (Sql Command 명령어 = 연결.Create Command()) {     명령어.명령어 텍스트 = "사용자 이름 = @select AND ROOM = @room 사용자 이름에서 선택";     명령어.파라미터.부가가치("@filength", 사용자 이름);     명령어.파라미터.부가가치("@room", 방);      사용. (SqlDataReader 데이터 판독기 = 명령어.이그제큐트 리더())     {         // ...     } }

ADO.NETSqlCommand모든 타입을 사용할 수 있습니다.value파라미터AddWithValue타입 변환은 자동으로 이루어집니다.명명된 매개변수(즉, "named parameters")의 사용에 유의하십시오."@username")가 아니라"?": 쿼리 명령어텍스트 내에서 임의의 순서로 파라미터를 여러 번 사용할 수 있습니다.

단, AddWithValue 메서드는 varchar 및 nvarchar와 같은 가변 길이 데이터 유형에 사용할 수 없습니다.그 이유는.NET은 리플렉션을 통해 데이터베이스에서 실제 길이를 얻는 것이 아니라 파라미터의 길이를 지정된 값의 길이로 가정합니다.그 결과 길이마다 다른 쿼리 계획이 컴파일되고 저장됩니다.일반적으로 "복제" 계획의 최대 수는 데이터베이스에 지정된 가변 길이 열의 길이의 곱입니다.따라서 가변 길이 열에는 표준 추가 방법을 사용하는 것이 중요합니다.

command.Parameters.Add(ParamName, VarChar, ParamLength).Value = ParamValue여기서 ParamLength는 데이터베이스에서 지정된 길이입니다.

표준 Add 메서드는 가변 길이 데이터 유형에 사용해야 하므로 모든 매개 변수 유형에 사용하는 것이 좋습니다.

Python DB-API

다음 예제에서는 Python 및 DB-API를 사용합니다.

수입품 mysql.mysql.discl  와 함께 mysql.커넥터.연결하다(데이터베이스="실패", 유저="루트") ~하듯이 접속하다:     와 함께 접속하다.커서(준비가 된=진실의) ~하듯이 커서:         커서.실행하다("제품이 없는 경우 테이블 생성(VARCHAR(40), 가격 INT)")         파라미터 = [('자전거", 10900),                   ("실패", 7400),                   ("전화", 29500)]         커서.모든 실행("제품의 가치에 삽입"%s,%s)", 파라미터)         파라미터 = ("실패",)         커서.실행하다("SELECT * FROM 제품 이름 =%s", 파라미터)         인쇄물(커서.페치올()[0][1])

Magic Direct SQL

이 예에서는 eDeveloper, uniPaaS 및 Magic Software Enterprise의 매직XPA와 같은 제4세대 언어의 Direct SQL을 사용합니다.

가상 사용자 이름 Alpha 20 init: 'sister' 가상 비밀번호 Alpha 20 init: 'yellow' SQL 명령:SELECT * FROM users WHERE USERNAME=:1 AND PASSWORD=:2입력 인수: 1: 사용자 이름 2: 비밀번호

Pure Basic

PureBasic(v5.40 LTS 이후)은 다음 명령어로 7종류의 링크를 관리할 수 있습니다.

SetDatabaseBlob, SetDatabaseDouble, SetDatabaseFloat, SetDatabaseLong, SetDatabaseNull, SetDatabaseQuad, SetDatabaseString

데이터베이스 유형에 따라 두 가지 방법이 있습니다.

SQLite, ODBC, MariaDB/Mysql의 경우: ?

  Set Database String(#데이터베이스, 0, '테스트")     한다면 데이터베이스쿼리(#데이터베이스, "SELECT * FROM 직원 ID=?")         ; ...   엔드 If

포스트그레용SQL 사용: $1, $2, $3, ... 

  Set Database String(#데이터베이스, 0, "스미스") ; -> $1    Set Database String(#데이터베이스, 1, "네")   ; -> $2   SetDatabaseLong  (#데이터베이스, 2, 50)      ; -> $3     한다면 데이터베이스쿼리(#데이터베이스, "Select * FROM 종업원 ID = $1 and active = $2 and years > $3")         ; ...   엔드 If

레퍼런스

  1. ^ a b The PHP Documentation Group. "Prepared statements and stored procedures". PHP Manual. Retrieved 25 September 2011.
  2. ^ Petrunia, Sergey (28 April 2007). "MySQL Optimizer and Prepared Statements". Sergey Petrunia's blog. Retrieved 25 September 2011.
  3. ^ Zaitsev, Peter (2 August 2006). "MySQL Prepared Statements". MySQL Performance Blog. Retrieved 25 September 2011.
  4. ^ "7.6.3.1. How the Query Cache Operates". MySQL 5.1 Reference Manual. Oracle. Retrieved 26 September 2011.
  5. ^ "Prepared Statement Objects". SQLite. 18 Oct 2021.{{cite web}}: CS1 maint :url-status (링크)
  6. ^ Oracle. "20.9.4. C API Prepared Statements". MySQL 5.5 Reference Manual. Retrieved 27 March 2012.
  7. ^ "13 Oracle Dynamic SQL". Pro*C/C++ Precompiler Programmer's Guide, Release 9.2. Oracle. Retrieved 25 September 2011.
  8. ^ "Using the PREPARE and EXECUTE statements". i5/OS Information Center, Version 5 Release 4. IBM. Retrieved 25 September 2011.[영구 데드링크]
  9. ^ "SQL Server 2008 R2: Preparing SQL Statements". MSDN Library. Microsoft. Retrieved 25 September 2011.
  10. ^ "PREPARE". PostgreSQL 9.5.1 Documentation. PostgreSQL Global Development Group. Retrieved 27 February 2016.
  11. ^ Oracle. "12.6. SQL Syntax for Prepared Statements". MySQL 5.5 Reference Manual. Retrieved 27 March 2012.
  12. ^ "Using Prepared Statements". The Java Tutorials. Oracle. Retrieved 25 September 2011.
  13. ^ Bunce, Tim. "DBI-1.616 specification". CPAN. Retrieved 26 September 2011.
  14. ^ "Python PEP 289: Python Database API Specification v2.0".
  15. ^ "SQL Injections: How Not To Get Stuck". The Codist. 8 May 2007. Retrieved February 1, 2010.