Programming

드롭 다운을 사용하는 경우 SQL 주입을 방지해야합니까?

procodes 2020. 8. 25. 21:32
반응형

드롭 다운을 사용하는 경우 SQL 주입을 방지해야합니까?


주로 SQL 삽입의 기회로 인해 양식의 사용자 입력을 신뢰해서는 안된다는 것을 이해합니다.

그러나 이것은 드롭 다운에서 유일한 입력 인 양식에도 적용됩니까 (아래 참조)?

나는 $_POST['size']세션 에을 저장하고 있으며, 사이트 전체에서 다양한 데이터베이스 ( mysqli선택 쿼리 사용) 를 쿼리하는 데 사용되며 모든 SQL 주입은 확실히 해를 끼칠 것입니다 (아마도 삭제).

데이터베이스를 쿼리하기위한 입력 된 사용자 입력 영역이 없으며 드롭 다운 만 있습니다.

<form action="welcome.php" method="post">
<select name="size">
  <option value="All">Select Size</option> 
  <option value="Large">Large</option>
  <option value="Medium">Medium</option>
  <option value="Small">Small</option>
</select>
<input type="submit">
</form>

게시 된 크기가 예상 한 크기인지 확인하기 위해 다음 예제와 같이 간단한 작업을 수행 할 수 있습니다.

$possibleOptions = array('All', 'Large', 'Medium', 'Small');

if(in_array($_POST['size'], $possibleOptions)) {
    // Expected
} else {
    // Not Expected
}

그런 다음 결과를 저장하려면 php> = 5.3.0 버전을 사용하는 경우 mysqli_ *를 사용하십시오. 올바르게 사용하면 SQL 주입에 도움이됩니다.


네, 이것으로부터 보호해야합니다.

Firefox의 개발자 콘솔을 사용하여 이유를 보여 드리겠습니다.

드롭 다운의 값 중 하나를 드롭 테이블 문으로 편집했습니다.

이 데이터를 정리하지 않으면 데이터베이스가 파괴됩니다. (이것은 완전히 유효한 SQL 문이 아닐 수도 있지만 제 요점을 이해했으면합니다.)

드롭 다운에서 사용할 수있는 옵션을 제한했다고 해서 내가 서버에 보낼 수있는 데이터가 제한 되었다는 의미는 아닙니다 .

페이지에서 동작을 사용하여 이것을 추가로 제한하려는 경우 내 옵션에는 해당 동작을 비활성화하거나 어쨌든이 양식 제출을 모방하는 사용자 지정 HTTP 요청을 서버에 작성하는 것이 포함됩니다. 이라는 도구의 정확히 사용은, 내가 생각하는 명령은 다음과 같이 보일 것이다 어쨌든이 SQL 주입 제출 :

curl --data "size=%27%29%3B%20DROP%20TABLE%20*%3B%20--"  http://www.example.com/profile/save

(이것은 완전히 유효한 curl 명령이 아닐 수도 있지만, 다시 말하지만 제 요점을 이해했으면합니다.)

그래서 반복하겠습니다.

사용자 입력을 신뢰하지 마십시오. 항상 자신을 보호하십시오.

사용자 입력이 안전하다고 가정하지 마십시오. 양식이 아닌 다른 수단을 통해 도착하더라도 잠재적으로 안전하지 않습니다. SQL 주입으로부터 자신을 보호하는 것을 잊을만큼 신뢰할 수있는 것은 없습니다.


이 질문에 태그가 지정 다음은 이러한 특정 유형의 공격에 대한 답변입니다.

주석에서 언급했듯이 모든 변수 데이터를 포함 하는 모든 단일 쿼리에 대해 준비된 문 예외없이 사용해야 합니다.

HTML에 관계없이!
SQL 쿼리는 HTML 입력 또는 기타 모든 외부 요인에 관계없이 올바르게 형식화되어야 함을 이해하는 것이 중요합니다 .

입력 유효성 검사 목적으로 다른 답변에서 제안 된 화이트리스트를 사용할 수 있지만 SQL 관련 작업에는 영향을주지 않아야합니다. HTML 입력의 유효성을 검사했는지 여부에 관계없이 동일하게 유지되어야합니다. 이는 쿼리에 변수를 추가 할 때 준비된 문을 사용해야 함을 의미합니다.

여기에서 철저한 설명, 준비된 명령문이 필수 인 이유, 적절하게 사용하는 방법 및 적용 할 수없는 경우에 수행 할 작업을 찾을 수 있습니다. The Hitchhiker 's Guide to SQL Injection protection

또한이 질문에는 태그가 지정되었습니다 . 대부분 우연이라고 생각하지만 어쨌든 원시 mysqli 가 이전 mysq_ * 함수를 적절하게 대체하지 않는다는 점을 경고해야 합니다 . 단순히 이전 스타일로 사용하면 보안이 전혀 추가되지 않기 때문입니다. 준비된 명령문에 대한 지원은 고통스럽고 번거롭지 만 일반 PHP 사용자는 전혀 노력할 수 없을 정도입니다. 따라서 ORM 또는 일종의 추상화 라이브러리가 옵션이 아닌 경우 PDO 가 유일한 선택입니다.


예.

누구나 실제로 전송 된 값에 대해 무엇이든 스푸핑 할 수 있습니다.

SO, for validating dropdown menus, you can just check to make sure that the value that you're working with was in the dropdown - something like this would be the best(most sanely paranoid) way:

if(in_array($_POST['ddMenu'], $dropDownValues){

    $valueYouUseLaterInPDO = $dropDownValues[array_search("two", $arr)];

} else {

    die("effin h4x0rs! Keep off my LAMP!!"); 

}

One way of protecting against users changing your drop downs using the console is to only use integer values in them. Then you can validate that the POST value contains an integer, and use an array to convert that to text when needed. E.g:

<?php
// No, you don't need to specify the numbers in the array but as we're using them I always find having them visually there helpful.
$sizes = array(0 => 'All', 1 => 'Large', 2 => 'Medium', 3 => 'Small');
$size = filter_input(INPUT_POST, "size", FILTER_VALIDATE_INT);

echo '<select name="size">';
foreach($sizes as $i => $s) {
    echo '<option value="' . $i . '"' . ($i == $size ? ' selected' : '') . '>' . $s . '</option>';
}
echo '</select>';

Then you can use $size in your query with knowledge that it will only ever contain FALSE or an integer.


The other answers already cover what you need to know. But maybe it helps to clarify some more:

There are TWO THINGS you need to do:

1. Validate form data.

As Jonathan Hobbs' answer shows very clearly, the choice of html element for the form input does not do any reliable filtering for you.

Validation is usually done in a way that does not alter the data, but that shows the form again, with the fields marked as "Please correct this".

Most frameworks and CMSes have form builders that help you with this task. And not just that, they also help against CSRF (or "XSRF"), which is another form of attack.

2. Sanitize/Escape variables in SQL statements..

.. or let prepared statements do the job for you.

If you build a (My)SQL statement with any variables, user-provided or not, you need to escape and quote these variables.

Generally, any such variable you insert into a MySQL statement should be either a string, or something that PHP can be reliably turn into a string that MySQL can digest. Such as, numbers.

For strings, you then need to choose one of several methods to escape the string, that means, replace any characters that would have side effects in MySQL.

  • In old-school MySQL + PHP, mysql_real_escape_string() does the job. The problem is that it is far too easy to forget, so you should absolutely use prepared statements or query builders.
  • In MySQLi, you can use prepared statements.
  • Most frameworks and CMSes provide query builders that help you with this task.

If you are dealing with a number, you could omit the escaping and the quotes (this is why the prepared statements allow to specify a type).

It is important to point out that you escape the variables for the SQL statement, and NOT for the database itself. The database will store the original string, but the statement needs an escaped version.

What happens if you omit one of these?

If you don't use form validation, but you do sanitize your SQL input, you might see all kinds of bad stuff happening, but you won't see SQL injection! (*)

First, it can take your application into a state you did not plan for. E.g. if you want to calculate the average age of all users, but one user gave "aljkdfaqer" for the age, your calculation will fail.

Secondly, there can be all kinds of other injection attacks you need to consider: E.g. the user input could contain javascript or other stuff.

There can still be problems with the database: E.g. if a field (database table column) is limited to 255 characters, and the string is longer than that. Or if the field only accepts numbers, and you attempt to save a non-numeric string instead. But this is not "injection", it is just "crashing the application".

But, even if you have a free text field where you allow any input with no validation at all, you could still save this to the database just like that, if you properly escape it when it goes to a database statement. The problem comes when you want to use this string somewhere.

(*) or this would be something really exotic.

If you don't escape variables for SQL statements, but you did validate form input, then you can still see bad stuff happening.

First, you risk that when you save data to the database and load it again, it won't be the same data anymore, "lost in translation".

Secondly, it can result in invalid SQL statements, and thus crash your application. E.g. if any variable contains a quote or double quote character, depending which type of quote you use, you will get invalid MySQL statement.

Thirdly, it can still cause SQL injection.

If your user input from forms is already filtered / validated, intentional SQl injection may become less likely, IF your input is reduced to a hardcoded list of options, or if it is restricted to numbers. But any free text input can be used for SQL injection, if you don't properly escape the variables in SQL statements.

And even if you have no form input at all, you could still have strings from all kinds of sources: Read from the filesystem, scraped from the internet, etc. Noone can guarantee that these strings are safe.


Your web browser does not "know" that it is receiving a page from php, all it sees is html. And the http layer knows even less than that. You need to be able to handle nearly any kind of input that can cross the http layer (luckily for most input php will already give an error). If you are trying to prevent malicious requests from messing up your db, then you need to assume that the guy on the other end knows what he is doing, and that he is not limited to what you can see in your browser under normal circumstances (not to mention what you can fiddle with a browser's developer tools). So yes, you need to cater for any input from your dropdown, but for most input you can give an error.


The fact that you have restricted the user to only using values from a certain drop-down list is irrelevant. A technical user can capture the http request sent to your server before it leaves their network, alter it using a tool such as a local proxy server, and then continue it on it's way. Using the altered request, they can send parameter values that are not ones that you have specified in the drop down list. Developers have to have the mindset that client restrictions are often meaningless, as anything on a client can be altered. Server validation is required at every single point that client data enters. Attackers rely on the naivety of developers in this sole aspect.


It's best to use a parameterized query in order to ensure against SQL injection. In that case the look of the query would be this:

SELECT * FROM table WHERE size = ?

When you supply a query like the above with text that is unverified for integrity (the input isn't validated on the server) and it contains SQL injection code it will be handled correctly. In other words, the request will result in something like this happening in the database layer:

SELECT * FROM table WHERE size = 'DROP table;'

This will simply select 0 results as it returns which will make the query ineffective in actually causing harm to the database without the need for a whitelist, a verification check or other techniques. Please note that a responsible programmer will do security in layers, and will often validate in addition to parameterizing queries. However, there is very little cause to not parameterize your queries from a performance perspective and the security added by this practice is a good reason to familiarize yourself with parameterized queries.


Whatever is submitted from your form comes to your server as text across the wires. There is nothing stopping anyone from creating a bot to mimic the client or type it in from a terminal if they wanted to. Never assume that because you programmed the client it will act like you think it will. This is really easy to spoof.

Example of what can and will happen when you trust the client.


A hacker can bypass the browser completely, including Javascript form checking, by sending a request using Telnet. Of course, he will look at the code of your html page to get the field names he has to use, but from then on it's 'everything goes' for him. So, you must check all values submitted on the server as if they did not originate from your html page.

참고 URL : https://stackoverflow.com/questions/22534183/do-i-have-to-guard-against-sql-injection-if-i-used-a-dropdown

반응형