PDO 준비 문이 SQL 주입을 방지하기에 충분합니까?

PDO 준비 문이 SQL 주입을 방지하기에 충분합니까?

---

다음과 같은 코드가 있다고 가정 해 보겠습니다.

$dbh = new PDO("blahblah");

$stmt = $dbh->prepare('SELECT * FROM users where username = :username');
$stmt->execute( array(':username' => $_REQUEST['username']) );

PDO 문서는 다음과 같이 말합니다.

준비된 명령문의 매개 변수는 인용 할 필요가 없습니다. 운전자가 처리합니다.

SQL 인젝션을 피하기 위해 내가해야 할 일이 그게 전부입니까? 정말 그렇게 쉬운가요?

차이가 있다면 MySQL을 가정 할 수 있습니다. 또한 SQL 주입에 대해 준비된 문을 사용하는 것에 대해서만 궁금합니다. 이 맥락에서 저는 XSS 또는 기타 가능한 취약점에 대해 신경 쓰지 않습니다.

---

짧은 대답은 아니오입니다 . PDO 준비는 가능한 모든 SQL-Injection 공격으로부터 사용자를 보호하지 않습니다. 특정 모호한 가장자리 케이스의 경우.

나는 PDO에 대해 이야기하기 위해이 대답 을 채택 하고 있습니다 ...

긴 대답은 그렇게 쉽지 않습니다. 여기에서 시연 된 공격을 기반으로합니다 .

공격

자, 먼저 공격을 보여 주면서 ...

$pdo->query('SET NAMES gbk');
$var = "\xbf\x27 OR 1=1 /*";
$query = 'SELECT * FROM test WHERE name = ? LIMIT 1';
$stmt = $pdo->prepare($query);
$stmt->execute(array($var));

특정 상황에서는 두 개 이상의 행을 반환합니다. 여기서 무슨 일이 일어나고 있는지 분석해 보겠습니다.

1. 문자 집합 선택

   $pdo->query('SET NAMES gbk');
   

   작업이 공격을 위해, 우리는 서버의 두 인코딩에 대한 연결을 기대하는 인코딩을 필요 'ASCII의 예에서와 같이 0x27 하고 , 그 최종 바이트는 ASCII 일부 문자를 가지고 \즉 0x5c. 그것이 나오는 것에 따라, 기본적으로 MySQL의 5.6에서 지원 (5)와 같은 인코딩이있다 : big5, cp932, gb2312, gbk와 sjis. gbk여기서 선택 하겠습니다.

   자, SET NAMES여기서 의 사용법에 주목하는 것이 매우 중요합니다 . 이것은 서버에 문자 집합을 설정합니다 . 다른 방법이 있지만 곧 도착할 것입니다.

2. 페이로드

   이 주입에 사용할 페이로드는 바이트 시퀀스로 시작합니다 0xbf27. 에서는 gbk잘못된 멀티 바이트 문자입니다. 에서는 latin1문자열 ¿'입니다. 에 그주의 latin1 하고 gbk , 0x27자신이 리터럴에 '문자.

   이 페이로드를 선택했습니다. 호출 하면 문자 앞에 addslashes()ASCII \ie를 삽입 0x5c하기 때문 '입니다. 우리가 바람 것 그래서 0xbf5c27, 어떤에서이 gbk두 개의 문자 순서입니다 : 0xbf5c다음에 0x27. 즉, 유효한 문자 뒤에 이스케이프 처리되지 않은 '. 그러나 우리는 addslashes(). 그래서 다음 단계로 넘어갑니다 ...

3. $ stmt-> execute ()

   여기서 알아 두어야 할 중요한 것은 PDO가 기본적으로 실제 준비된 문을 수행 하지 않는다는 것입니다. 그것들을 에뮬레이트합니다 (MySQL의 경우). 따라서 PDO는 mysql_real_escape_string()바인딩 된 각 문자열 값에 대해 (MySQL C API 함수)를 호출하여 쿼리 문자열을 내부적으로 빌드합니다 .

   에 대한 C API 호출 은 연결 문자 집합을 알고 있다는 mysql_real_escape_string()점에서 다릅니다 addslashes(). 따라서 서버가 예상하는 문자 집합에 대해 이스케이프를 올바르게 수행 할 수 있습니다. 그러나이 시점까지 클라이언트는 우리가 latin1달리 말하지 않았기 때문에 우리 가 여전히 연결에 사용하고 있다고 생각합니다 . 우리는 서버에 우리가 사용하고 있다고 말 gbk했지만 클라이언트는 여전히 latin1.

   따라서 mysql_real_escape_string()백 슬래시 를 삽입 하는 호출은 '"이스케이프 된"콘텐츠에 자유롭게 매달려있는 문자가 있습니다! 우리가보고 있다면 사실, $var에서 gbk문자 집합, 우리는 볼 것 :

   縗 'OR 1 = 1 / *

   이것이 바로 공격에 필요한 것입니다.

4. 쿼리

   This part is just a formality, but here's the rendered query:

   SELECT * FROM test WHERE name = '縗' OR 1=1 /*' LIMIT 1
   

Congratulations, you just successfully attacked a program using PDO Prepared Statements...

The Simple Fix

Now, it's worth noting that you can prevent this by disabling emulated prepared statements:

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

This will usually result in a true prepared statement (i.e. the data being sent over in a separate packet from the query). However, be aware that PDO will silently fallback to emulating statements that MySQL can't prepare natively: those that it can are listed in the manual, but beware to select the appropriate server version).

The Correct Fix

The problem here is that we didn't call the C API's mysql_set_charset() instead of SET NAMES. If we did, we'd be fine provided we are using a MySQL release since 2006.

If you're using an earlier MySQL release, then a bug in mysql_real_escape_string() meant that invalid multibyte characters such as those in our payload were treated as single bytes for escaping purposes even if the client had been correctly informed of the connection encoding and so this attack would still succeed. The bug was fixed in MySQL 4.1.20, 5.0.22 and 5.1.11.

But the worst part is that PDO didn't expose the C API for mysql_set_charset() until 5.3.6, so in prior versions it cannot prevent this attack for every possible command! It's now exposed as a DSN parameter, which should be used instead of SET NAMES...

The Saving Grace

As we said at the outset, for this attack to work the database connection must be encoded using a vulnerable character set. utf8mb4 is not vulnerable and yet can support every Unicode character: so you could elect to use that instead—but it has only been available since MySQL 5.5.3. An alternative is utf8, which is also not vulnerable and can support the whole of the Unicode Basic Multilingual Plane.

Alternatively, you can enable the NO_BACKSLASH_ESCAPES SQL mode, which (amongst other things) alters the operation of mysql_real_escape_string(). With this mode enabled, 0x27 will be replaced with 0x2727 rather than 0x5c27 and thus the escaping process cannot create valid characters in any of the vulnerable encodings where they did not exist previously (i.e. 0xbf27 is still 0xbf27 etc.)—so the server will still reject the string as invalid. However, see @eggyal's answer for a different vulnerability that can arise from using this SQL mode (albeit not with PDO).

Safe Examples

The following examples are safe:

mysql_query('SET NAMES utf8');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Because the server's expecting utf8...

mysql_set_charset('gbk');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Because we've properly set the character set so the client and the server match.

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->query('SET NAMES gbk');
$stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$stmt->execute(array("\xbf\x27 OR 1=1 /*"));

Because we've turned off emulated prepared statements.

$pdo = new PDO('mysql:host=localhost;dbname=testdb;charset=gbk', $user, $password);
$stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$stmt->execute(array("\xbf\x27 OR 1=1 /*"));

Because we've set the character set properly.

$mysqli->query('SET NAMES gbk');
$stmt = $mysqli->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$param = "\xbf\x27 OR 1=1 /*";
$stmt->bind_param('s', $param);
$stmt->execute();

Because MySQLi does true prepared statements all the time.

Wrapping Up

If you:

• Use Modern Versions of MySQL (late 5.1, all 5.5, 5.6, etc) AND PDO's DSN charset parameter (in PHP ≥ 5.3.6)

OR

• Don't use a vulnerable character set for connection encoding (you only use utf8 / latin1 / ascii / etc)

OR

• Enable NO_BACKSLASH_ESCAPES SQL mode

You're 100% safe.

Otherwise, you're vulnerable even though you're using PDO Prepared Statements...

Addendum

I've been slowly working on a patch to change the default to not emulate prepares for a future version of PHP. The problem that I'm running into is that a LOT of tests break when I do that. One problem is that emulated prepares will only throw syntax errors on execute, but true prepares will throw errors on prepare. So that can cause issues (and is part of the reason tests are borking).

---

Prepared statements / parameterized queries are generally sufficient to prevent 1st order injection on that statement^*. If you use un-checked dynamic sql anywhere else in your application you are still vulnerable to 2nd order injection.

2nd order injection means data has been cycled through the database once before being included in a query, and is much harder to pull off. AFAIK, you almost never see real engineered 2nd order attacks, as it is usually easier for attackers to social-engineer their way in, but you sometimes have 2nd order bugs crop up because of extra benign ' characters or similar.

You can accomplish a 2nd order injection attack when you can cause a value to be stored in a database that is later used as a literal in a query. As an example, let's say you enter the following information as your new username when creating an account on a web site (assuming MySQL DB for this question):

' + (SELECT UserName + '_' + Password FROM Users LIMIT 1) + '

If there are no other restrictions on the username, a prepared statement would still make sure that the above embedded query doesn't execute at the time of insert, and store the value correctly in the database. However, imagine that later the application retrieves your username from the database, and uses string concatenation to include that value a new query. You might get to see someone else's password. Since the first few names in users table tend to be admins, you may have also just given away the farm. (Also note: this is one more reason not to store passwords in plain text!)

We see, then, that prepared statements are enough for a single query, but by themselves they are not sufficient to protect against sql injection attacks throughout an entire application, because they lack a mechanism to enforce that all access to a database within the application uses safe code. However, used as part of good application design — which may include practices such as code review or static analysis, or use of an ORM, data layer, or service layer that limits dynamic sql — prepared statements are the primary tool for solving the Sql Injection problem. If you follow good application design principles, such that your data access is separated from the rest of your program, it becomes easy to enforce or audit that every query correctly uses parameterization. In this case, sql injection (both first and second order) is completely prevented.

---

_{^*It turns out that MySql/PHP are (okay, were) just dumb about handling parameters when wide characters are involved, and there is still a rare case outlined in the other highly-voted answer here that can allow injection to slip through a parameterized query.}

---

No, they are not always.

It depends on whether you allow user input to be placed within the query itself. For example:

$dbh = new PDO("blahblah");

$tableToUse = $_GET['userTable'];

$stmt = $dbh->prepare('SELECT * FROM ' . $tableToUse . ' where username = :username');
$stmt->execute( array(':username' => $_REQUEST['username']) );

would be vulnerable to SQL injections and using prepared statements in this example won't work, because the user input is used as an identifier, not as data. The right answer here would be to use some sort of filtering/validation like:

$dbh = new PDO("blahblah");

$tableToUse = $_GET['userTable'];
$allowedTables = array('users','admins','moderators');
if (!in_array($tableToUse,$allowedTables))    
 $tableToUse = 'users';

$stmt = $dbh->prepare('SELECT * FROM ' . $tableToUse . ' where username = :username');
$stmt->execute( array(':username' => $_REQUEST['username']) );

Note: you can't use PDO to bind data that goes outside of DDL (Data Definition Language), i.e. this does not work:

$stmt = $dbh->prepare('SELECT * FROM foo ORDER BY :userSuppliedData');

The reason why the above does not work is because DESC and ASC are not data. PDO can only escape for data. Secondly, you can't even put ' quotes around it. The only way to allow user chosen sorting is to manually filter and check that it's either DESC or ASC.

---

Yes, it is sufficient. The way injection type attacks work, is by somehow getting an interpreter (The database) to evaluate something, that should have been data, as if it was code. This is only possible if you mix code and data in the same medium (Eg. when you construct a query as a string).

Parameterised queries work by sending the code and the data separately, so it would never be possible to find a hole in that.

You can still be vulnerable to other injection-type attacks though. For example, if you use the data in a HTML-page, you could be subject to XSS type attacks.

---

No this is not enough (in some specific cases)! By default PDO uses emulated prepared statements when using MySQL as a database driver. You should always disable emulated prepared statements when using MySQL and PDO:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Another thing that always should be done it set the correct encoding of the database:

$dbh = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');

Also see this related question: How can I prevent SQL injection in PHP?

Also note that that only is about the database side of the things you would still have to watch yourself when displaying the data. E.g. by using htmlspecialchars() again with the correct encoding and quoting style.

---

Personally I would always run some form of sanitation on the data first as you can never trust user input, however when using placeholders / parameter binding the inputted data is sent to the server separately to the sql statement and then binded together. The key here is that this binds the provided data to a specific type and a specific use and eliminates any opportunity to change the logic of the SQL statement.

---

Eaven if you are going to prevent sql injection front-end, using html or js checks, you'd have to consider that front-end checks are "bypassable".

You can disable js or edit a pattern with a front-end development tool (built in with firefox or chrome nowadays).

So, in order to prevent SQL injection, would be right to sanitize input date backend inside your controller.

I would like to suggest to you to use filter_input() native PHP function in order to sanitize GET and INPUT values.

If you want to go ahead with security, for sensible database queries, I'd like to suggest to you to use regular expression to validate data format. preg_match() will help you in this case! But take care! Regex engine is not so light. Use it only if necessary, otherwise your application performances will decrease.

Security has a costs, but do not waste your performance!

Easy example:

if you want to double check if a value, received from GET is a number, less then 99 if(!preg_match('/[0-9]{1,2}/')){...} is heavyer of

if (isset($value) && intval($value)) <99) {...}

So, the final answer is: "No! PDO Prepared Statements does not prevent all kind of sql injection"; It does not prevent unexpected values, just unexpected concatenation

참고URL : https://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection