PHP database security - SQL injection and preventive measures

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.

这些攻击总是建立在发掘安全意识不强的代码上的。所以，永远不要信任外界输入的数据，特别是来自于客户端的，包括选择框、表单隐藏域和 cookie。就如上面的第一个例子那样，就算是正常的查询也有可能造成灾难。

永远不要使用超级用户或所有者帐号去连接数据库。要用权限被严格限制的帐号。

检查输入的数据是否具有所期望的数据格式。PHP 有很多可以用于检查输入的函数，从简单的变量函数和字符类型函数（比如 is_numeric()， ctype_digit()）到复杂的Perl 兼容正则表达式函数都可以完成这个工作。

如果程序等待输入一个数字，可以考虑使用 is_numeric() 来检查，或者直接使用 settype() 来转换它的类型，也可以用 sprintf() 把它格式化为数字。

Example #5 一个实现分页更安全的方法

<?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);
?>

使用数据库特定的敏感字符转义函数（比如 mysql_escape_string() 和 sql_escape_string()）把用户提交上来的非数字数据进行转义。如果数据库没有专门的敏感字符转义功能的话 addslashes() 和 str_replace() 可以代替完成这个工作。看看第一个例子，此例显示仅在查询的静态部分加上引号是不够的，查询很容易被攻破。

要不择手段避免显示出任何有关数据库的信心，尤其是数据库结构。

也可以选择使用数据库的存储过程和预定义指针等特性来抽象数库访问，使用户不能直接访问数据表和视图。但这个办法又有别的影响。

除此之外，在允许的情况下，使用代码或数据库系统保存查询日志也是一个好办法。显然，日志并不能防止任何攻击，但利用它可以跟踪到哪个程序曾经被尝试攻击过。日志本身没用，要查阅其中包含的信息才行。毕竟，更多的信息总比没有要好。