2020年3月21日 星期六

The problem of executing sql query in phpmyadmin. The reason is a firewall executes security policies to defense sql injection.

PHPMyAdmin error message
---
Error in processing request
Error text: error (rejected)
It seems that the connection to server has been lost. Please check your network connectivity and server status.
---
因為資料庫教學,使用xampp架設了http server&mysql server,
但學校不開放mysql的port,想說用phpmyadmin做連線
但在家中卻發現,下sql指令時卻一定產生上面的訊息
花了一段時間才找到問題,就是因為學校防火牆會把http中的sql語句擋下來
為了防止SQL Injection...(這真的是無意義又惱人的資安問題)
所以只好進行sql語句的編碼.

If the firewall uses security policies to prevent SQL Injection, it will cause this problem.

Solution:
When the front-end send a sql statement to the backend, first use base64encoder  to encode the sql statement, after the backend receives this sql statement, use base64decoder to decode the sql statement.

front-end: jQuery Base64Ecnoder  --> https://gist.github.com/TaoK/1602210
back-end: php Base64Encoder


sql.js
$(document).on('submit', '#sqlqueryform.ajax', function (event) {
add:
$form.find('textarea[name="sql_query"]').val($.base64Encode($form.find('textarea[name="sql_query"]').val()));

after
// Coming from a bookmark dialog
...
} elseif (isset($_GET['sql_query']) && isset($_GET['sql_signature'])) {
    if (Core::checkSqlQuerySignature($_GET['sql_query'], $_GET['sql_signature'])) {
        $sql_query = $_GET['sql_query'];
    }
}
to add:
if(Util::is_base64($sql_query))  //some sql_query was encoded, but some wasn't.
$sql_query=base64_decode($sql_query); //encoded sql_query must be decoded.

header.php //include javascripts file
private function _addDefaultScripts(): void
    {
add:
$this->_scripts->addFile('jQuery.base64.js');

sql.php
after:
// Coming from a bookmark dialog

if (isset($_POST['bkm_fields']['bkm_sql_query'])) {
...
}
add:
$sql_query=base64_decode($sql_query);

functions.js
 $(document).on('click', 'input#sql_query_edit_save', function () {
add:
sqlQuery=$.base64Encode(sqlQuery);

Util.php
htmlspecialchars($sql_query)-->
htmlspecialchars(base64_encode($sql_query))

Results.php
htmlspecialchars($this->__get('sql_query'))
-->
htmlspecialchars(base64_encode($this->__get('sql_query')))

'sql_query'          => $this->__get('sql_query'),
-->
'sql_query'          => base64_encode($this->__get('sql_query')),

'sql_query' => $this->__get('sql_query'),
-->
'sql_query' => base64_encode($this->__get('sql_query')),

$this->__get('sql_query'),
-->
base64_encode($this->__get('sql_query')),

tbl_row_action.php

if (isset($original_sql_query)) {
                $sql_query = $original_sql_query;
            }

-->
if (isset($original_sql_query)) {
                $sql_query = base64_decode($original_sql_query);
            }

tbl_operations.php
$this_sql_query = 'TRUNCATE TABLE '
. Util::backquote($table);
add:

$this_sql_query=base64_encode($this_sql_query);

$this_sql_query = 'DROP TABLE '
. Util::backquote($table);
add:

StructureController.php
'drop_query' => $drop_query,
modified:
'drop_query' => base64_encode($drop_query),
...
not yet finish...---
Util.php:
The below code can't be modified, because it should be able to be edited in the inline editor of the front-end.

$retval .= Url::getHiddenInputs($GLOBALS['db'], $GLOBALS['table']);
            $retval .= '<input type="hidden" name="sql_query" value="'
                . htmlspecialchars($sql_query) . '">';//Yotrew:SQL行內編輯,不編碼


// Display the SQL query and link to MySQL documentation.
...
$error_msg .= '    </p>'. "\n"
             
. '<p>' . "\n"
             
. $formatted_sql . "\n"
-->
if(is_base64($formatted_sql))
$formatted_sql=base64_decode($formatted_sql);//Yotrew:顯示SQL敍述給使用者看,所以要解碼
            $error_msg .= '    </p>' . "\n"
                . '<p>' . "\n"
                . $formatted_sql. "\n"
                . '</p>' . "\n";

add is_utf8 &s_base64 function
//Ref:https://www.itread01.com/p/1415528.html
function is_utf8($str){
$len = strlen($str);
for($i = 0; $i < $len; $i++){
$c = ord($str[$i]);
if($c > 128){
if(($c > 247)){
return false;
}elseif($c > 239){
$bytes = 4;
}elseif($c > 223){
$bytes = 3;
}elseif ($c > 191){
$bytes = 2;
}else{
return false;
}
if(($i + $bytes) > $len){
return false;
}
while($bytes > 1){
$i++;
$b = ord($str[$i]);
if($b < 128 || $b > 191){
return false;
}
$bytes--;
}
}
}
return true;
}
//判斷是否base64加密
function is_base64($str){
//這裡多了個純字母和純數字的正則判斷
if(@preg_match('/^[0-9]*$/',$str) || @preg_match('/^[a-zA-Z]*$/',$str)){
return false;
}elseif(is_utf8(base64_decode($str)) && base64_decode($str) != ''){
return true;
}
return false;
}
---

沒有留言:

張貼留言