Làm cách nào để ngăn chặn việc đưa SQL vào PHP?
Nếu đầu vào của người dùng được chèn mà không sửa đổi vào truy vấn SQL, thì ứng dụng sẽ dễ bị chèn SQL , như trong ví dụ sau:
$unsafe_variable = $_POST['user_input'];
mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");
Đó là bởi vì người dùng có thể nhập một cái gì đó như value'); DROP TABLE table;--
và truy vấn trở thành:
INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')
Có thể làm gì để ngăn điều này xảy ra?
Trả lời
Sử dụng các câu lệnh chuẩn bị sẵn và các truy vấn được tham số hóa. Đây là các câu lệnh SQL được máy chủ cơ sở dữ liệu gửi đến và phân tích cú pháp riêng biệt với bất kỳ tham số nào. Bằng cách này, kẻ tấn công không thể đưa SQL độc hại vào.
Về cơ bản, bạn có hai lựa chọn để đạt được điều này:
Sử dụng PDO (cho bất kỳ trình điều khiển cơ sở dữ liệu được hỗ trợ nào):
$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name'); $stmt->execute([ 'name' => $name ]); foreach ($stmt as $row) { // Do something with $row }
Sử dụng MySQLi (cho MySQL):
$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?'); $stmt->bind_param('s', $name); // 's' specifies the variable type => 'string' $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { // Do something with $row }
Nếu bạn đang kết nối với cơ sở dữ liệu không phải MySQL, có một tùy chọn thứ hai dành riêng cho trình điều khiển mà bạn có thể tham khảo (ví dụ: pg_prepare()
và pg_execute()
cho PostgreSQL). PDO là tùy chọn phổ biến.
Thiết lập kết nối chính xác
Lưu ý rằng khi sử dụng PDO
để truy cập cơ sở dữ liệu MySQL, các câu lệnh chuẩn bị thực không được sử dụng theo mặc định . Để khắc phục điều này, bạn phải tắt tính năng mô phỏng các câu lệnh đã chuẩn bị. Ví dụ về việc tạo kết nối bằng PDO là:
$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'password');
$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
Trong ví dụ trên, chế độ lỗi không hoàn toàn cần thiết, nhưng bạn nên thêm nó vào . Bằng cách này, tập lệnh sẽ không dừng lại Fatal Error
khi có sự cố. Và nó cho nhà phát triển cơ hội đối với catch
bất kỳ (các) lỗi nào là throw
n là PDOException
s.
Tuy nhiên, điều bắt buộc là setAttribute()
dòng đầu tiên , dòng này yêu cầu PDO vô hiệu hóa các câu lệnh chuẩn bị giả lập và sử dụng các câu lệnh chuẩn bị thực sự . Điều này đảm bảo rằng câu lệnh và các giá trị không được phân tích cú pháp bởi PHP trước khi gửi nó đến máy chủ MySQL (không cho kẻ tấn công có cơ hội đưa SQL độc hại vào).
Mặc dù bạn có thể đặt các charset
tùy chọn của hàm tạo, nhưng điều quan trọng cần lưu ý là các phiên bản PHP 'cũ hơn' (trước 5.3.6) đã âm thầm bỏ qua tham số bộ ký tự trong DSN.
Giải trình
Câu lệnh SQL mà bạn chuyển đến prepare
được máy chủ cơ sở dữ liệu phân tích cú pháp và biên dịch. Bằng cách chỉ định các tham số (một ?
hoặc một tham số được đặt tên như :name
trong ví dụ ở trên), bạn cho cơ sở dữ liệu biết nơi bạn muốn lọc. Sau đó, khi bạn gọi execute
, câu lệnh đã chuẩn bị được kết hợp với các giá trị tham số mà bạn chỉ định.
Điều quan trọng ở đây là các giá trị tham số được kết hợp với câu lệnh đã biên dịch, không phải là một chuỗi SQL. SQL injection hoạt động bằng cách lừa tập lệnh bao gồm các chuỗi độc hại khi nó tạo SQL để gửi đến cơ sở dữ liệu. Vì vậy, bằng cách gửi SQL thực tế riêng biệt với các tham số, bạn hạn chế rủi ro kết thúc với một cái gì đó bạn không có ý định.
Bất kỳ tham số nào bạn gửi khi sử dụng một câu lệnh đã chuẩn bị sẽ chỉ được coi là chuỗi (mặc dù công cụ cơ sở dữ liệu có thể thực hiện một số tối ưu hóa nên tất nhiên các tham số cũng có thể kết thúc dưới dạng số). Trong ví dụ trên, nếu $name
biến chứa 'Sarah'; DELETE FROM employees
kết quả sẽ chỉ đơn giản là tìm kiếm chuỗi "'Sarah'; DELETE FROM employees"
và bạn sẽ không kết thúc với một bảng trống .
Một lợi ích khác của việc sử dụng các câu lệnh đã soạn sẵn là nếu bạn thực thi cùng một câu lệnh nhiều lần trong cùng một phiên, nó sẽ chỉ được phân tích cú pháp và biên dịch một lần, giúp bạn tăng tốc độ.
Ồ, và vì bạn đã hỏi về cách thực hiện điều đó cho một đoạn chèn, đây là một ví dụ (sử dụng PDO):
$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)'); $preparedStatement->execute([ 'column' => $unsafeValue ]);
Các câu lệnh đã chuẩn bị có thể được sử dụng cho các truy vấn động không?
Mặc dù bạn vẫn có thể sử dụng các câu lệnh đã chuẩn bị sẵn cho các tham số truy vấn, nhưng bản thân cấu trúc của truy vấn động không thể được tham số hóa và không thể tham số hóa một số tính năng truy vấn nhất định.
Đối với những trường hợp cụ thể này, điều tốt nhất nên làm là sử dụng bộ lọc danh sách trắng hạn chế các giá trị có thể có.
// Value whitelist
// $dir can only be 'DESC', otherwise it will be 'ASC'
if (empty($dir) || $dir !== 'DESC') {
$dir = 'ASC';
}
Cảnh báo không được dùng nữa: Mã mẫu của câu trả lời này (giống như mã mẫu của câu hỏi) sử dụng
MySQL
tiện ích mở rộng của PHP , không được chấp nhận trong PHP 5.5.0 và bị xóa hoàn toàn trong PHP 7.0.0.Cảnh báo bảo mật : Câu trả lời này không phù hợp với các phương pháp bảo mật tốt nhất. Thoát không đủ để ngăn chặn SQL injection , hãy sử dụng các câu lệnh đã chuẩn bị thay thế. Sử dụng chiến lược nêu dưới đây với rủi ro của riêng bạn. (Ngoài ra,
mysql_real_escape_string()
đã bị xóa trong PHP 7.)
Nếu bạn đang sử dụng phiên bản PHP gần đây, mysql_real_escape_string
tùy chọn được nêu bên dưới sẽ không còn khả dụng (mặc dù mysqli::escape_string
là phiên bản tương đương hiện đại). Ngày nay, mysql_real_escape_string
tùy chọn này chỉ có ý nghĩa đối với mã kế thừa trên phiên bản PHP cũ.
Bạn có hai tùy chọn - thoát các ký tự đặc biệt trong của bạn unsafe_variable
hoặc sử dụng truy vấn được tham số hóa. Cả hai đều sẽ bảo vệ bạn khỏi SQL injection. Truy vấn tham số được coi là phương pháp tốt hơn nhưng sẽ yêu cầu thay đổi thành phần mở rộng MySQL mới hơn trong PHP trước khi bạn có thể sử dụng nó.
Chúng tôi sẽ đề cập đến việc thoát khỏi chuỗi tác động thấp hơn trước.
//Connect
$unsafe_variable = $_POST["user-input"];
$safe_variable = mysql_real_escape_string($unsafe_variable);
mysql_query("INSERT INTO table (column) VALUES ('" . $safe_variable . "')");
//Disconnect
Xem thêm, các chi tiết của mysql_real_escape_stringchức năng.
Để sử dụng truy vấn được tham số hóa, bạn cần sử dụng MySQLi thay vì các hàm MySQL . Để viết lại ví dụ của bạn, chúng tôi cần một cái gì đó như sau.
<?php
$mysqli = new mysqli("server", "username", "password", "database_name");
// TODO - Check that connection was successful.
$unsafe_variable = $_POST["user-input"];
$stmt = $mysqli->prepare("INSERT INTO table (column) VALUES (?)");
// TODO check that $stmt creation succeeded // "s" means the database expects a string $stmt->bind_param("s", $unsafe_variable); $stmt->execute();
$stmt->close(); $mysqli->close();
?>
Chức năng chính bạn muốn đọc ở đó sẽ có mysqli::prepare.
Ngoài ra, như những người khác đã đề xuất, bạn có thể thấy hữu ích / dễ dàng hơn khi nâng cấp một lớp trừu tượng với một cái gì đó như PDO .
Xin lưu ý rằng trường hợp bạn hỏi là một trường hợp khá đơn giản và các trường hợp phức tạp hơn có thể yêu cầu các cách tiếp cận phức tạp hơn. Đặc biệt:
- Nếu bạn muốn thay đổi cấu trúc của SQL dựa trên đầu vào của người dùng, các truy vấn được tham số hóa sẽ không giúp ích được gì và yêu cầu thoát không được đề cập
mysql_real_escape_string
. Trong trường hợp này, tốt hơn hết bạn nên chuyển đầu vào của người dùng qua danh sách trắng để đảm bảo chỉ các giá trị 'an toàn' mới được phép thông qua. - Nếu bạn sử dụng số nguyên từ đầu vào của người dùng trong một điều kiện và thực hiện
mysql_real_escape_string
phương pháp này, bạn sẽ gặp phải vấn đề được mô tả bởi Đa thức trong các nhận xét bên dưới. Trường hợp này phức tạp hơn vì các số nguyên sẽ không được bao quanh bởi dấu ngoặc kép, vì vậy bạn có thể giải quyết bằng cách xác thực rằng đầu vào của người dùng chỉ chứa các chữ số. - Có thể có những trường hợp khác mà tôi không biết. Bạn có thể thấy đây là một nguồn hữu ích về một số vấn đề phức tạp hơn mà bạn có thể gặp phải.
Mọi câu trả lời ở đây chỉ bao gồm một phần của vấn đề. Trên thực tế, có bốn phần truy vấn khác nhau mà chúng ta có thể thêm động vào SQL: -
- một chuỗi
- một số
- một định danh
- một từ khóa cú pháp
Và các báo cáo chuẩn bị chỉ bao gồm hai trong số đó.
Nhưng đôi khi chúng ta phải làm cho truy vấn của mình động hơn nữa, thêm cả toán tử hoặc số nhận dạng. Vì vậy, chúng ta sẽ cần các kỹ thuật bảo vệ khác nhau.
Nói chung, cách tiếp cận bảo vệ như vậy dựa trên danh sách trắng .
Trong trường hợp này, mọi thông số động phải được mã hóa cứng trong tập lệnh của bạn và được chọn từ tập hợp đó. Ví dụ, để thực hiện đặt hàng động:
$orders = array("name", "price", "qty"); // Field names $key = array_search($_GET['sort'], $orders)); // if we have such a name
$orderby = $orders[$key]; // If not, first one will be set automatically. $query = "SELECT * FROM `table` ORDER BY $orderby"; // Value is safe
Để giảm bớt quy trình, tôi đã viết một hàm trợ giúp danh sách trắng thực hiện tất cả công việc trong một dòng:
$orderby = white_list($_GET['orderby'], "name", ["name","price","qty"], "Invalid field name"); $query = "SELECT * FROM `table` ORDER BY `$orderby`"; // sound and safe
Có một cách khác để bảo mật số nhận dạng - thoát nhưng tôi thích sử dụng danh sách trắng hơn như một cách tiếp cận rõ ràng và mạnh mẽ hơn. Tuy nhiên, miễn là bạn có mã định danh được trích dẫn, bạn có thể thoát ký tự trích dẫn để đảm bảo an toàn. Ví dụ, theo mặc định đối với mysql, bạn phải nhân đôi ký tự trích dẫn để thoát khỏi nó . Đối với các quy tắc thoát DBMS khác sẽ khác.
Tuy nhiên, có một vấn đề với các từ khóa cú pháp SQL (chẳng hạn như AND
, DESC
v.v.), nhưng danh sách trắng dường như là cách tiếp cận duy nhất trong trường hợp này.
Vì vậy, một khuyến nghị chung có thể được diễn giải như
- Bất kỳ biến nào đại diện cho một ký tự dữ liệu SQL, (hoặc nói một cách đơn giản - một chuỗi SQL hoặc một số) phải được thêm vào thông qua một câu lệnh đã chuẩn bị. Không có ngoại lệ.
- Bất kỳ phần truy vấn nào khác, chẳng hạn như từ khóa SQL, bảng hoặc tên trường hoặc toán tử - phải được lọc qua danh sách trắng.
Cập nhật
Mặc dù có một thỏa thuận chung về các phương pháp hay nhất liên quan đến bảo vệ SQL injection, nhưng vẫn có nhiều phương pháp không tốt. Và một số trong số chúng đã ăn sâu vào tâm trí người dùng PHP. Ví dụ: trên chính trang này có (mặc dù không nhìn thấy đối với hầu hết khách truy cập) hơn 80 câu trả lời đã bị xóa - tất cả đều bị cộng đồng xóa do chất lượng kém hoặc quảng bá các phương pháp không tốt và lỗi thời. Tệ hơn nữa, một số câu trả lời không hợp lệ không bị xóa, mà còn đang thịnh vượng.
Ví dụ: (1) còn (2) vẫn còn (3) nhiều (4) câu trả lời (5) , bao gồm cả câu trả lời được ủng hộ nhiều thứ hai đề xuất bạn thoát chuỗi thủ công - một cách tiếp cận lỗi thời được chứng minh là không an toàn.
Hoặc có một câu trả lời tốt hơn một chút chỉ gợi ý một phương pháp định dạng chuỗi khác và thậm chí tự hào nó là phương thuốc chữa bách bệnh cuối cùng. Trong khi tất nhiên, nó không phải là. Phương pháp này không tốt hơn so với định dạng chuỗi thông thường, nhưng nó vẫn giữ tất cả các nhược điểm của nó: nó chỉ áp dụng cho các chuỗi và giống như bất kỳ định dạng thủ công nào khác, về cơ bản nó là biện pháp tùy chọn, không bắt buộc, dễ xảy ra lỗi do con người gây ra.
Tôi nghĩ rằng tất cả những điều này là do một sự mê tín rất cũ, được hỗ trợ bởi các cơ quan chức năng như OWASP hoặc sổ tay PHP , công bố sự bình đẳng giữa bất cứ điều gì "thoát" và bảo vệ khỏi việc tiêm SQL.
Bất kể những gì hướng dẫn sử dụng PHP nói cho các lứa tuổi, *_escape_string
không có nghĩa là làm cho dữ liệu an toàn và không bao giờ được dự định. Ngoài việc vô dụng đối với bất kỳ phần SQL nào ngoài chuỗi, thoát thủ công là sai, vì nó là thủ công ngược lại với tự động.
Và OWASP thậm chí còn làm cho nó tồi tệ hơn, nhấn mạnh vào việc thoát khỏi đầu vào của người dùng , một điều hoàn toàn vô nghĩa: không nên có những từ như vậy trong ngữ cảnh bảo vệ tiêm. Mọi biến đều tiềm ẩn nguy cơ - bất kể nguồn nào! Hay nói cách khác - mọi biến phải được định dạng đúng để được đưa vào truy vấn - bất kể nguồn lại là gì. Đó là điểm đến quan trọng. Thời điểm một nhà phát triển bắt đầu tách cừu khỏi dê (suy nghĩ xem biến cụ thể nào đó có "an toàn" hay không) thì anh ấy / cô ấy thực hiện bước đầu tiên của mình đối với thảm họa. Chưa kể rằng ngay cả từ ngữ cũng gợi ý thoát hàng loạt tại điểm nhập, giống như tính năng trích dẫn rất kỳ diệu - đã bị coi thường, không được chấp nhận và bị loại bỏ.
Vì vậy, không giống như bất cứ điều gì "thoát", các câu lệnh được chuẩn bị sẵn là biện pháp thực sự bảo vệ khỏi SQL injection (khi có thể).
Tôi khuyên bạn nên sử dụng PDO (Đối tượng dữ liệu PHP) để chạy các truy vấn SQL được tham số hóa.
Điều này không chỉ bảo vệ khỏi SQL injection mà còn tăng tốc các truy vấn.
And by using PDO rather than mysql_
, mysqli_
, and pgsql_
functions, you make your application a little more abstracted from the database, in the rare occurrence that you have to switch database providers.
Use PDO
and prepared queries.
($conn
is a PDO
object)
$stmt = $conn->prepare("INSERT INTO tbl VALUES(:id, :name)"); $stmt->bindValue(':id', $id); $stmt->bindValue(':name', $name); $stmt->execute();
As you can see, people suggest you use prepared statements at the most. It's not wrong, but when your query is executed just once per process, there would be a slight performance penalty.
I was facing this issue, but I think I solved it in very sophisticated way - the way hackers use to avoid using quotes. I used this in conjunction with emulated prepared statements. I use it to prevent all kinds of possible SQL injection attacks.
My approach:
If you expect input to be integer make sure it's really integer. In a variable-type language like PHP it is this very important. You can use for example this very simple but powerful solution:
sprintf("SELECT 1,2,3 FROM table WHERE 4 = %u", $input);
If you expect anything else from integer hex it. If you hex it, you will perfectly escape all input. In C/C++ there's a function called mysql_hex_string(), in PHP you can use bin2hex().
Don't worry about that the escaped string will have a 2x size of its original length because even if you use
mysql_real_escape_string
, PHP has to allocate same capacity((2*input_length)+1)
, which is the same.This hex method is often used when you transfer binary data, but I see no reason why not use it on all data to prevent SQL injection attacks. Note that you have to prepend data with
0x
or use the MySQL functionUNHEX
instead.
So, for example, the query:
SELECT password FROM users WHERE name = 'root';
Will become:
SELECT password FROM users WHERE name = 0x726f6f74;
or
SELECT password FROM users WHERE name = UNHEX('726f6f74');
Hex is the perfect escape. No way to inject.
Difference between UNHEX function and 0x prefix
There was some discussion in comments, so I finally want to make it clear. These two approaches are very similar, but they are a little different in some ways:
The 0x
prefix can only be used for data columns such as char
, varchar
, text
, block
, binary
, etc.
Also, its use is a little complicated if you are about to insert an empty string. You'll have to entirely replace it with ''
, or you'll get an error.
UNHEX()
works on any column; you do not have to worry about the empty string.
Hex methods are often used as attacks
Note that this hex method is often used as an SQL injection attack where integers are just like strings and escaped just with mysql_real_escape_string
. Then you can avoid the use of quotes.
For example, if you just do something like this:
"SELECT title FROM article WHERE id = " . mysql_real_escape_string($_GET["id"])
an attack can inject you very easily. Consider the following injected code returned from your script:
SELECT ... WHERE id = -1 UNION ALL SELECT table_name FROM information_schema.tables;
and now just extract table structure:
SELECT ... WHERE id = -1 UNION ALL SELECT column_name FROM information_schema.column WHERE table_name = __0x61727469636c65__;
And then just select whatever data ones want. Isn't it cool?
But if the coder of an injectable site would hex it, no injection would be possible because the query would look like this:
SELECT ... WHERE id = UNHEX('2d312075...3635');
Deprecated Warning: This answer's sample code (like the question's sample code) uses PHP's
MySQL
extension, which was deprecated in PHP 5.5.0 and removed entirely in PHP 7.0.0.Security Warning: This answer is not in line with security best practices. Escaping is inadequate to prevent SQL injection, use prepared statements instead. Use the strategy outlined below at your own risk. (Also,
mysql_real_escape_string()
was removed in PHP 7.)IMPORTANT
The best way to prevent SQL Injection is to use Prepared Statements instead of escaping, as the accepted answer demonstrates.
There are libraries such as Aura.Sql and EasyDB that allow developers to use prepared statements easier. To learn more about why prepared statements are better at stopping SQL injection, refer to this mysql_real_escape_string() bypass and recently fixed Unicode SQL Injection vulnerabilities in WordPress.
Injection prevention - mysql_real_escape_string()
PHP has a specially-made function to prevent these attacks. All you need to do is use the mouthful of a function, mysql_real_escape_string
.
mysql_real_escape_string
takes a string that is going to be used in a MySQL query and return the same string with all SQL injection attempts safely escaped. Basically, it will replace those troublesome quotes(') a user might enter with a MySQL-safe substitute, an escaped quote \'.
NOTE: you must be connected to the database to use this function!
// Connect to MySQL
$name_bad = "' OR 1'"; $name_bad = mysql_real_escape_string($name_bad); $query_bad = "SELECT * FROM customers WHERE username = '$name_bad'"; echo "Escaped Bad Injection: <br />" . $query_bad . "<br />";
$name_evil = "'; DELETE FROM customers WHERE 1 or username = '"; $name_evil = mysql_real_escape_string($name_evil); $query_evil = "SELECT * FROM customers WHERE username = '$name_evil'"; echo "Escaped Evil Injection: <br />" . $query_evil;
You can find more details in MySQL - SQL Injection Prevention.
You could do something basic like this:
$safe_variable = mysqli_real_escape_string($_POST["user-input"], $dbConnection); mysqli_query($dbConnection, "INSERT INTO table (column) VALUES ('" . $safe_variable . "')");
This won't solve every problem, but it's a very good stepping stone. I left out obvious items such as checking the variable's existence, format (numbers, letters, etc.).
Whatever you do end up using, make sure that you check your input hasn't already been mangled by magic_quotes
or some other well-meaning rubbish, and if necessary, run it through stripslashes
or whatever to sanitize it.
Deprecated Warning: This answer's sample code (like the question's sample code) uses PHP's
MySQL
extension, which was deprecated in PHP 5.5.0 and removed entirely in PHP 7.0.0.Security Warning: This answer is not in line with security best practices. Escaping is inadequate to prevent SQL injection, use prepared statements instead. Use the strategy outlined below at your own risk. (Also,
mysql_real_escape_string()
was removed in PHP 7.)
Parameterized query AND input validation is the way to go. There are many scenarios under which SQL injection may occur, even though mysql_real_escape_string()
has been used.
Those examples are vulnerable to SQL injection:
$offset = isset($_GET['o']) ? $_GET['o'] : 0; $offset = mysql_real_escape_string($offset); RunQuery("SELECT userid, username FROM sql_injection_test LIMIT $offset, 10");
or
$order = isset($_GET['o']) ? $_GET['o'] : 'userid'; $order = mysql_real_escape_string($order); RunQuery("SELECT userid, username FROM sql_injection_test ORDER BY `$order`");
In both cases, you can't use '
to protect the encapsulation.
Source: The Unexpected SQL Injection (When Escaping Is Not Enough)
In my opinion, the best way to generally prevent SQL injection in your PHP application (or any web application, for that matter) is to think about your application's architecture. If the only way to protect against SQL injection is to remember to use a special method or function that does The Right Thing every time you talk to the database, you are doing it wrong. That way, it's just a matter of time until you forget to correctly format your query at some point in your code.
Adopting the MVC pattern and a framework like CakePHP or CodeIgniter is probably the right way to go: Common tasks like creating secure database queries have been solved and centrally implemented in such frameworks. They help you to organize your web application in a sensible way and make you think more about loading and saving objects than about securely constructing single SQL queries.
There are many ways of preventing SQL injections and other SQL hacks. You can easily find it on the Internet (Google Search). Of course PDO is one of the good solutions. But I would like to suggest you some good links prevention from SQL injection.
What is SQL injection and how to prevent
PHP manual for SQL injection
Microsoft explanation of SQL injection and prevention in PHP
And some other like Preventing SQL injection with MySQL and PHP.
Now, why you do you need to prevent your query from SQL injection?
I would like to let you know: Why do we try for preventing SQL injection with a short example below:
Query for login authentication match:
$query="select * from users where email='".$_POST['email']."' and password='".$_POST['password']."' ";
Now, if someone (a hacker) puts
$_POST['email']= [email protected]' OR '1=1
and password anything....
The query will be parsed into the system only up to:
$query="select * from users where email='[email protected]' OR '1=1';
The other part will be discarded. So, what will happen? A non-authorized user (hacker) will be able to log in as administrator without having his/her password. Now, he/she can do anything that the administrator/email person can do. See, it's very dangerous if SQL injection is not prevented.
I favor stored procedures (MySQL has had stored procedures support since 5.0) from a security point of view - the advantages are -
- Most databases (including MySQL) enable user access to be restricted to executing stored procedures. The fine-grained security access control is useful to prevent escalation of privileges attacks. This prevents compromised applications from being able to run SQL directly against the database.
- They abstract the raw SQL query from the application so less information of the database structure is available to the application. This makes it harder for people to understand the underlying structure of the database and design suitable attacks.
- They accept only parameters, so the advantages of parameterized queries are there. Of course - IMO you still need to sanitize your input - especially if you are using dynamic SQL inside the stored procedure.
The disadvantages are -
- They (stored procedures) are tough to maintain and tend to multiply very quickly. This makes managing them an issue.
- They are not very suitable for dynamic queries - if they are built to accept dynamic code as parameters then a lot of the advantages are negated.
I think if someone wants to use PHP and MySQL or some other dataBase server:
- Think about learning PDO (PHP Data Objects) – it is a database access layer providing a uniform method of access to multiple databases.
- Think about learning MySQLi
- Use native PHP functions like: strip_tags, mysql_real_escape_string or if variable numeric, just
(int)$foo
. Read more about type of variables in PHP here. If you're using libraries such as PDO or MySQLi, always use PDO::quote() and mysqli_real_escape_string().
Libraries examples:
---- PDO
----- No placeholders - ripe for SQL injection! It's bad
$request = $pdoConnection->("INSERT INTO parents (name, addr, city) values ($name, $addr, $city)");
----- Unnamed placeholders
$request = $pdoConnection->("INSERT INTO parents (name, addr, city) values (?, ?, ?);
----- Named placeholders
$request = $pdoConnection->("INSERT INTO parents (name, addr, city) value (:name, :addr, :city)");
--- MySQLi
$request = $mysqliConnection->prepare('
SELECT * FROM trainers
WHERE name = ?
AND email = ?
AND last_login > ?');
$query->bind_param('first_param', 'second_param', $mail, time() - 3600);
$query->execute();
P.S:
PDO wins this battle with ease. With support for twelve different database drivers and named parameters, we can ignore the small performance loss, and get used to its API. From a security standpoint, both of them are safe as long as the developer uses them the way they are supposed to be used
But while both PDO and MySQLi are quite fast, MySQLi performs insignificantly faster in benchmarks – ~2.5% for non-prepared statements, and ~6.5% for prepared ones.
And please test every query to your database - it's a better way to prevent injection.
If possible, cast the types of your parameters. But it's only working on simple types like int, bool, and float.
$unsafe_variable = $_POST['user_id'];
$safe_variable = (int)$unsafe_variable ;
mysqli_query($conn, "INSERT INTO table (column) VALUES ('" . $safe_variable . "')");
If you want to take advantage of cache engines, like Redis or Memcached, maybe DALMP could be a choice. It uses pure MySQLi. Check this: DALMP Database Abstraction Layer for MySQL using PHP.
Also, you can 'prepare' your arguments before preparing your query so that you can build dynamic queries and at the end have a fully prepared statements query. DALMP Database Abstraction Layer for MySQL using PHP.
For those unsure of how to use PDO (coming from the mysql_
functions), I made a very, very simple PDO wrapper that is a single file. It exists to show how easy it is to do all the common things applications need to be done. Works with PostgreSQL, MySQL, and SQLite.
Basically, read it while you read the manual to see how to put the PDO functions to use in real life to make it simple to store and retrieve values in the format you want.
I want a single column
$count = DB::column('SELECT COUNT(*) FROM `user`);
I want an array(key => value) results (i.e. for making a selectbox)
$pairs = DB::pairs('SELECT `id`, `username` FROM `user`);
I want a single row result
$user = DB::row('SELECT * FROM `user` WHERE `id` = ?', array($user_id));
I want an array of results
$banned_users = DB::fetch('SELECT * FROM `user` WHERE `banned` = ?', array(TRUE));
Using this PHP function mysql_escape_string()
you can get a good prevention in a fast way.
For example:
SELECT * FROM users WHERE name = '".mysql_escape_string($name_from_html_form)."'
mysql_escape_string
— Escapes a string for use in a mysql_query
For more prevention, you can add at the end ...
wHERE 1=1 or LIMIT 1
Finally you get:
SELECT * FROM users WHERE name = '".mysql_escape_string($name_from_html_form)."' LIMIT 1
A few guidelines for escaping special characters in SQL statements.
Don't use MySQL. This extension is deprecated. Use MySQLi or PDO instead.
MySQLi
For manually escaping special characters in a string you can use the mysqli_real_escape_string function. The function will not work properly unless the correct character set is set with mysqli_set_charset.
Example:
$mysqli = new mysqli('host', 'user', 'password', 'database'); $mysqli->set_charset('charset');
$string = $mysqli->real_escape_string($string); $mysqli->query("INSERT INTO table (column) VALUES ('$string')");
For automatic escaping of values with prepared statements, use mysqli_prepare, and mysqli_stmt_bind_param where types for the corresponding bind variables must be provided for an appropriate conversion:
Example:
$stmt = $mysqli->prepare("INSERT INTO table (column1, column2) VALUES (?,?)"); $stmt->bind_param("is", $integer, $string);
$stmt->execute();
No matter if you use prepared statements or mysqli_real_escape_string
, you always have to know the type of input data you're working with.
So if you use a prepared statement, you must specify the types of the variables for mysqli_stmt_bind_param
function.
And the use of mysqli_real_escape_string
is for, as the name says, escaping special characters in a string, so it will not make integers safe. The purpose of this function is to prevent breaking the strings in SQL statements, and the damage to the database that it could cause. mysqli_real_escape_string
is a useful function when used properly, especially when combined with sprintf
.
Example:
$string = "x' OR name LIKE '%John%";
$integer = '5 OR id != 0'; $query = sprintf( "SELECT id, email, pass, name FROM members WHERE email ='%s' AND id = %d", $mysqli->real_escape_string($string), $integer); echo $query;
// SELECT id, email, pass, name FROM members WHERE email ='x\' OR name LIKE \'%John%' AND id = 5
$integer = '99999999999999999999'; $query = sprintf("SELECT id, email, pass, name FROM members WHERE email ='%s' AND id = %d", $mysqli->real_escape_string($string), $integer); echo $query;
// SELECT id, email, pass, name FROM members WHERE email ='x\' OR name LIKE \'%John%' AND id = 2147483647
The simple alternative to this problem could be solved by granting appropriate permissions in the database itself. For example: if you are using a MySQL database then enter into the database through terminal or the UI provided and just follow this command:
GRANT SELECT, INSERT, DELETE ON database TO username@'localhost' IDENTIFIED BY 'password';
This will restrict the user to only get confined with the specified query's only. Remove the delete permission and so the data would never get deleted from the query fired from the PHP page. The second thing to do is to flush the privileges so that the MySQL refreshes the permissions and updates.
FLUSH PRIVILEGES;
more information about flush.
To see the current privileges for the user fire the following query.
select * from mysql.user where User='username';
Learn more about GRANT.
Regarding many useful answers, I hope to add some value to this thread.
SQL injection is an attack that can be done through user inputs (inputs that filled by a user and then used inside queries). The SQL injection patterns are correct query syntax while we can call it: bad queries for bad reasons, and we assume that there might be a bad person that try to get secret information (bypassing access control) that affect the three principles of security (confidentiality, integrity, and availability).
Now, our point is to prevent security threats such as SQL injection attacks, the question asking (how to prevent an SQL injection attack using PHP), be more realistic, data filtering or clearing input data is the case when using user-input data inside such query, using PHP or any other programming language is not the case, or as recommended by more people to use modern technology such as prepared statement or any other tools that currently supporting SQL injection prevention, consider that these tools not available anymore? How do you secure your application?
My approach against SQL injection is: clearing user-input data before sending it to the database (before using it inside any query).
Data filtering for (converting unsafe data to safe data)
Consider that PDO and MySQLi are not available. How can you secure your application? Do you force me to use them? What about other languages other than PHP? I prefer to provide general ideas as it can be used for wider border, not just for a specific language.
- SQL user (limiting user privilege): most common SQL operations are (SELECT, UPDATE, INSERT), then, why give the UPDATE privilege to a user that does not require it? For example, login, and search pages are only using SELECT, then, why use DB users in these pages with high privileges?
RULE: do not create one database user for all privileges. For all SQL operations, you can create your scheme like (deluser, selectuser, updateuser) as usernames for easy usage.
See principle of least privilege.
Data filtering: before building any query user input, it should be validated and filtered. For programmers, it's important to define some properties for each user-input variables: data type, data pattern, and data length. A field that is a number between (x and y) must be exactly validated using the exact rule, and for a field that is a string (text): pattern is the case, for example, a username must contain only some characters, let’s say [a-zA-Z0-9_-.]. The length varies between (x and n) where x and n (integers, x <=n). Rule: creating exact filters and validation rules are best practices for me.
Use other tools: Here, I will also agree with you that a prepared statement (parametrized query) and stored procedures. The disadvantages here is these ways require advanced skills which do not exist for most users. The basic idea here is to distinguish between the SQL query and the data that is used inside. Both approaches can be used even with unsafe data, because the user-input data here does not add anything to the original query, such as (any or x=x).
For more information, please read OWASP SQL Injection Prevention Cheat Sheet.
Now, if you are an advanced user, start using this defense as you like, but, for beginners, if they can't quickly implement a stored procedure and prepared the statement, it's better to filter input data as much they can.
Finally, let's consider that a user sends this text below instead of entering his/her username:
[1] UNION SELECT IF(SUBSTRING(Password,1,1)='2',BENCHMARK(100000,SHA1(1)),0) User,Password FROM mysql.user WHERE User = 'root'
This input can be checked early without any prepared statement and stored procedures, but to be on the safe side, using them starts after user-data filtering and validation.
The last point is detecting unexpected behavior which requires more effort and complexity; it's not recommended for normal web applications.
Unexpected behavior in the above user input is SELECT, UNION, IF, SUBSTRING, BENCHMARK, SHA, and root. Once these words detected, you can avoid the input.
UPDATE 1:
A user commented that this post is useless, OK! Here is what OWASP.ORG provided:
Primary defenses:
Option #1: Use of Prepared Statements (Parameterized Queries)
Option #2: Use of Stored Procedures
Option #3: Escaping all User Supplied Input
Additional defenses:
Also Enforce: Least Privilege
Also Perform: White List Input Validation
As you may know, claiming an article should be supported by a valid argument, at least by one reference! Otherwise, it's considered as an attack and a bad claim!
Update 2:
From the PHP manual, PHP: Prepared Statements - Manual:
Escaping and SQL injection
Bound variables will be escaped automatically by the server. The server inserts their escaped values at the appropriate places into the statement template before execution. A hint must be provided to the server for the type of bound variable, to create an appropriate conversion. See the mysqli_stmt_bind_param() function for more information.
The automatic escaping of values within the server is sometimes considered a security feature to prevent SQL injection. The same degree of security can be achieved with non-prepared statements if input values are escaped correctly.
Update 3:
I created test cases for knowing how PDO and MySQLi send the query to the MySQL server when using a prepared statement:
PDO:
$user = "''1''"; // Malicious keyword $sql = 'SELECT * FROM awa_user WHERE userame =:username';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)); $sth->execute(array(':username' => $user));
Query Log:
189 Query SELECT * FROM awa_user WHERE userame ='\'\'1\'\'' 189 Quit
MySQLi:
$stmt = $mysqli->prepare("SELECT * FROM awa_user WHERE username =?")) { $stmt->bind_param("s", $user); $user = "''1''";
$stmt->execute();
Query Log:
188 Prepare SELECT * FROM awa_user WHERE username =? 188 Execute SELECT * FROM awa_user WHERE username ='\'\'1\'\'' 188 Quit
It's clear that a prepared statement is also escaping the data, nothing else.
As also mentioned in the above statement,
The automatic escaping of values within the server is sometimes considered a security feature to prevent SQL injection. The same degree of security can be achieved with non-prepared statements, if input values are escaped correctly
Therefore, this proves that data validation such as intval()
is a good idea for integer values before sending any query. In addition, preventing malicious user data before sending the query is a correct and valid approach.
Please see this question for more detail: PDO sends raw query to MySQL while Mysqli sends prepared query, both produce the same result
References:
- SQL Injection Cheat Sheet
- SQL Injection
- Information security
- Security Principles
- Data validation
Security Warning: This answer is not in line with security best practices. Escaping is inadequate to prevent SQL injection, use prepared statements instead. Use the strategy outlined below at your own risk. (Also,
mysql_real_escape_string()
was removed in PHP 7.)Deprecated Warning: The mysql extension is deprecated at this time. we recommend using the PDO extension
I use three different ways to prevent my web application from being vulnerable to SQL injection.
- Use of
mysql_real_escape_string()
, which is a pre-defined function in PHP, and this code add backslashes to the following characters:\x00
,\n
,\r
,\
,'
,"
and\x1a
. Pass the input values as parameters to minimize the chance of SQL injection. - The most advanced way is to use PDOs.
I hope this will help you.
Consider the following query:
$iId = mysql_real_escape_string("1 OR 1=1"); $sSql = "SELECT * FROM table WHERE id = $iId";
mysql_real_escape_string() will not protect here. If you use single quotes (' ') around your variables inside your query is what protects you against this. Here is an solution below for this:
$iId = (int) mysql_real_escape_string("1 OR 1=1"); $sSql = "SELECT * FROM table WHERE id = $iId";
This question has some good answers about this.
I suggest, using PDO is the best option.
Edit:
mysql_real_escape_string()
is deprecated as of PHP 5.5.0. Use either mysqli or PDO.
An alternative to mysql_real_escape_string() is
string mysqli_real_escape_string ( mysqli $link , string $escapestr )
Example:
$iId = $mysqli->real_escape_string("1 OR 1=1");
$mysqli->query("SELECT * FROM table WHERE id = $iId");
A simple way would be to use a PHP framework like CodeIgniter or Laravel which have inbuilt features like filtering and active-record so that you don't have to worry about these nuances.
Warning: the approach described in this answer only applies to very specific scenarios and isn't secure since SQL injection attacks do not only rely on being able to inject X=Y
.
If the attackers are trying to hack into the form via PHP's $_GET
variable or with the URL's query string, you would be able to catch them if they're not secure.
RewriteCond %{QUERY_STRING} ([0-9]+)=([0-9]+)
RewriteRule ^(.*) ^/track.php
Because 1=1
, 2=2
, 1=2
, 2=1
, 1+1=2
, etc... are the common questions to an SQL database of an attacker. Maybe also it's used by many hacking applications.
But you must be careful, that you must not rewrite a safe query from your site. The code above is giving you a tip, to rewrite or redirect (it depends on you) that hacking-specific dynamic query string into a page that will store the attacker's IP address, or EVEN THEIR COOKIES, history, browser, or any other sensitive information, so you can deal with them later by banning their account or contacting authorities.
A good idea is to use an object-relational mapper like Idiorm:
$user = ORM::for_table('user') ->where_equal('username', 'j4mie') ->find_one(); $user->first_name = 'Jamie';
$user->save(); $tweets = ORM::for_table('tweet')
->select('tweet.*')
->join('user', array(
'user.id', '=', 'tweet.user_id'
))
->where_equal('user.username', 'j4mie')
->find_many();
foreach ($tweets as $tweet) {
echo $tweet->text;
}
It not only saves you from SQL injections, but from syntax errors too! It also supports collections of models with method chaining to filter or apply actions to multiple results at once and multiple connections.
There are so many answers for PHP and MySQL, but here is code for PHP and Oracle for preventing SQL injection as well as regular use of oci8 drivers:
$conn = oci_connect($username, $password, $connection_string);
$stmt = oci_parse($conn, 'UPDATE table SET field = :xx WHERE ID = 123');
oci_bind_by_name($stmt, ':xx', $fieldval);
oci_execute($stmt);
Deprecated Warning: This answer's sample code (like the question's sample code) uses PHP's
MySQL
extension, which was deprecated in PHP 5.5.0 and removed entirely in PHP 7.0.0.Security Warning: This answer is not in line with security best practices. Escaping is inadequate to prevent SQL injection, use prepared statements instead. Use the strategy outlined below at your own risk. (Also,
mysql_real_escape_string()
was removed in PHP 7.)
Using PDO and MYSQLi is a good practice to prevent SQL injections, but if you really want to work with MySQL functions and queries, it would be better to use
mysql_real_escape_string
$unsafe_variable = mysql_real_escape_string($_POST['user_input']);
There are more abilities to prevent this: like identify - if the input is a string, number, char or array, there are so many inbuilt functions to detect this. Also, it would be better to use these functions to check input data.
is_string
$unsafe_variable = (is_string($_POST['user_input']) ? $_POST['user_input'] : '');
is_numeric
$unsafe_variable = (is_numeric($_POST['user_input']) ? $_POST['user_input'] : '');
And it is so much better to use those functions to check input data with mysql_real_escape_string
.
I've written this little function several years ago:
function sqlvprintf($query, $args)
{
global $DB_LINK; $ctr = 0;
ensureConnection(); // Connect to database if not connected already.
$values = array(); foreach ($args as $value) { if (is_string($value))
{
$value = "'" . mysqli_real_escape_string($DB_LINK, $value) . "'"; } else if (is_null($value))
{
$value = 'NULL'; } else if (!is_int($value) && !is_float($value)) { die('Only numeric, string, array and NULL arguments allowed in a query. Argument '.($ctr+1).' is not a basic type, it\'s type is '. gettype($value). '.'); } $values[] = $value; $ctr++;
}
$query = preg_replace_callback( '/{(\\d+)}/', function($match) use ($values) { if (isset($values[$match[1]])) { return $values[$match[1]]; } else { return $match[0];
}
},
$query ); return $query;
}
function runEscapedQuery($preparedQuery /*, ...*/) { $params = array_slice(func_get_args(), 1);
$results = runQuery(sqlvprintf($preparedQuery, $params)); // Run query and fetch results. return $results;
}
This allows running statements in an one-liner C#-ish String.Format like:
runEscapedQuery("INSERT INTO Whatever (id, foo, bar) VALUES ({0}, {1}, {2})", $numericVar, $stringVar1, $stringVar2);
It escapes considering the variable type. If you try to parameterize table, column names, it would fail as it puts every string in quotes which is an invalid syntax.
SECURITY UPDATE: The previous str_replace
version allowed injections by adding {#} tokens into user data. This preg_replace_callback
version doesn't cause problems if the replacement contains these tokens.