Many web developers don’t realize that SQL queries can be tampered with, so they treat SQL queries as trusted commands. Little did they know that SQL queries could bypass access controls, thereby bypassing authentication and permission checks. What's more, it's possible to run host operating system level commands via SQL queries.

Direct SQL command injection is a technique commonly used by attackers to create or modify existing SQL statements to obtain hidden data, overwrite key values, or even execute database host operating system commands. This is accomplished by the application taking user input and combining it with static parameters into an SQL query. Some real examples will be given below.

Due to the lack of validation of the entered data and using a superuser or other database account with the authority to create new users to connect, the attacker can create a new superuser in the database.

Example #1 A piece of code that implements paging display of data...can also be used to create a superuser (PostgreSQL system).

<?php
    $offset = $argv[0]; // 注意，沒有輸入驗證！
    $query = "SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET $offset;";
    $result = pg_query($conn, $query);
?>

General users will click on the "previous page" and "next page" links where $offset has been binned. The original code only thinks that $offset is a numerical value. However, if someone tries to urlencode() the following statement and then add it to the URL:

0;
insert into pg_shadow(usename,usesysid,usesuper,usecatupd,passwd)
    select 'crack', usesysid, 't','t','crack'
    from pg_shadow where usename='postgres';
--

then he can create a super user. Note that 0; is just to provide a correct offset to complete the original query so that it does not make errors.

Note:

-- is the comment mark of SQL, which can generally be used to tell the SQL interpreter to ignore the following statements.

A possible way to get the password is by targeting the page that displays the search results. All the attacker has to do is find out which variables were submitted for SQL statements and mishandled them. Such variables are usually used in conditional statements in SELECT queries, such as WHERE, ORDER BY, LIMIT and OFFSET. If the database supports the UNION construct, an attacker may also append a complete SQL query to the original statement to obtain the password from an arbitrary data table. Therefore, it is important to encrypt the password field.

Example #2 Display article... and some passwords (any database system)

<?php
    $query = "SELECT id, name, inserted, size FROM products
        WHERE size = &#39;$size&#39;
        ORDER BY $order LIMIT $limit, $offset;";
    $result = odbc_exec($conn, $query);
?>

You can add another SELECT query to the original query to get the passwords:

'
union select '1', concat(uname||'-'||passwd) as name, '1971-01-01', '0' from usertable;
--

If the above statement (using ' and --) If it is added to any variable in $query, then it will be troublesome.

UPDATE in SQL is also vulnerable. This query may also be inserted or appended to another complete request as in the example above. But attackers prefer to target the SET clause so they can change some data in the table. In this case, you must know the structure of the database in order to modify the query successfully. Fields can be guessed based on variable names on the form, or brute force cracked. There are not many ways to name the fields that store usernames and passwords.

Example #3 From resetting password... to gaining more permissions (any database system)

<?php
    $query = "UPDATE usertable SET pwd=&#39;$pwd&#39; WHERE uid=&#39;$uid&#39;;";
?>

But malicious users will submit ' or uid like '%admin%'; -- as the value of the variable to $uid Change the admin password, or submit the value of $pwd to "hehehe', admin='yes', trusted=100" (there is a space after it) to obtain more permissions. By doing this, the query actually becomes:

<?php
    // $uid == &#39; or uid like&#39;%admin%&#39;; --
    $query = "UPDATE usertable SET pwd=&#39;...&#39; WHERE uid=&#39;&#39; or uid like &#39;%admin%&#39;; --";
    // $pwd == "hehehe&#39;, admin=&#39;yes&#39;, trusted=100 "
    $query = "UPDATE usertable SET pwd=&#39;hehehe&#39;, admin=&#39;yes&#39;, trusted=100 WHERE
        ...;";
?>

The following horrific example will demonstrate how to execute system commands on some databases.

Example #4 Attack the operating system of the host where the database is located (MSSQL Server)

<?php
    $query  = "SELECT * FROM products WHERE id LIKE &#39;%$prod%&#39;";
    $result = mssql_query($query);
?>

If the attack submits a%' exec master..xp_cmdshell 'net user test testpass /ADD' -- as the value of the variable $prod, then $query will become

<?php
    $query = "SELECT * FROM products WHERE id LIKE &#39;%a%&#39;
        exec master..xp_cmdshell &#39;net user test testpass /ADD&#39;--";
    $result = mssql_query($query);
?>

MSSQL server will execute this SQL statement, including the command after it for adding users to the system. If this program is running as sa and the MSSQLSERVER service has sufficient permissions, the attacker can obtain a system account to access the host.

Note:

Although the above example is for a specific database system, it does not mean that similar attacks cannot be carried out on other database systems. Using different methods, various databases can suffer.

Precautionary Measures

Some people may comfort themselves by saying that the attacker needs to know the information about the database structure to carry out the above attack. Yes, it is. But no one can guarantee that attackers will not get this information. Once they do, the database is in danger of being leaked. If you are using an open source software package to access the database, such as a forum program, it is easy for an attacker to obtain the relevant code. The risk is even greater if the code is poorly designed.

這些攻擊總是建立在發(fā)掘安全意識不強的代碼上的。所以，永遠不要信任外界輸入的數(shù)據(jù)，特別是來自于客戶端的，包括選擇框、表單隱藏域和 cookie。就如上面的第一個例子那樣，就算是正常的查詢也有可能造成災(zāi)難。

永遠不要使用超級用戶或所有者帳號去連接數(shù)據(jù)庫。要用權(quán)限被嚴格限制的帳號。

檢查輸入的數(shù)據(jù)是否具有所期望的數(shù)據(jù)格式。PHP 有很多可以用于檢查輸入的函數(shù)，從簡單的變量函數(shù)和字符類型函數(shù)（比如 is_numeric()， ctype_digit()）到復(fù)雜的Perl 兼容正則表達式函數(shù)都可以完成這個工作。

如果程序等待輸入一個數(shù)字，可以考慮使用 is_numeric() 來檢查，或者直接使用 settype() 來轉(zhuǎn)換它的類型，也可以用 sprintf() 把它格式化為數(shù)字。

Example #5 一個實現(xiàn)分頁更安全的方法

<?php
    settype($offset, &#39;integer&#39;);
    $query = "SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET $offset;";
    // 請注意格式字符串中的 %d，如果用 %s 就毫無意義了
    $query = sprintf("SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET %d;",
        $offset);
?>

使用數(shù)據(jù)庫特定的敏感字符轉(zhuǎn)義函數(shù)（比如?mysql_escape_string()?和?sql_escape_string()）把用戶提交上來的非數(shù)字數(shù)據(jù)進行轉(zhuǎn)義。如果數(shù)據(jù)庫沒有專門的敏感字符轉(zhuǎn)義功能的話?addslashes()?和?str_replace()?可以代替完成這個工作?？纯吹谝粋€例子，此例顯示僅在查詢的靜態(tài)部分加上引號是不夠的，查詢很容易被攻破。

要不擇手段避免顯示出任何有關(guān)數(shù)據(jù)庫的信心，尤其是數(shù)據(jù)庫結(jié)構(gòu)。

也可以選擇使用數(shù)據(jù)庫的存儲過程和預(yù)定義指針等特性來抽象數(shù)庫訪問，使用戶不能直接訪問數(shù)據(jù)表和視圖。但這個辦法又有別的影響。

除此之外，在允許的情況下，使用代碼或數(shù)據(jù)庫系統(tǒng)保存查詢?nèi)罩疽彩且粋€好辦法。顯然，日志并不能防止任何攻擊，但利用它可以跟蹤到哪個程序曾經(jīng)被嘗試攻擊過。日志本身沒用，要查閱其中包含的信息才行。畢竟，更多的信息總比沒有要好。