How to Build a Secure SQL Query Tool for Private Deployments
This article explains why a SQL query tool is essential for on‑premise systems, describes the challenges of remote access and data safety, and provides a step‑by‑step implementation covering frontend encryption, backend whitelist checks, decryption, and query execution with code examples.
Application Scenario
In private on‑premise deployments the database is bound to 127.0.0.1 and is not exposed to external networks. When a bug occurs, external developers must obtain remote‑desktop access, possess SQL knowledge, and ensure data security, making troubleshooting cumbersome without a dedicated query interface.
SQL Query Tool Overview
An SQL query tool provides a graphical or command‑line interface for entering SQL statements, sending them to the DBMS, and displaying results. Typical UI components include a query editor, result pane, and object browser.
Implementation
Frontend
The visual interface is a web page that hosts a query editor, result pane and object browser. Client‑side encryption/decryption uses Crypto‑JS, a JavaScript cryptography library.
Download address: https://www.cdnpkg.com/crypto-js
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Open‑Source CryptoJS Encrypted SQL Query</title>
</head>
<body>
<script src="https://cdnjs.cloudflare.com/ajax/libs/crypto-js/4.0.0/crypto-js.js"></script>
<script>
/** 16‑byte hex string as key */
const ENCRYPT_KEY = 'yJFPEzGftZqNkwx';
/** 16‑byte hex string as IV */
const ENCRYPT_IV = '20243zyJFPEzhE0B';
const key = CryptoJS.enc.Utf8.parse(ENCRYPT_KEY);
const iv = CryptoJS.enc.Utf8.parse(ENCRYPT_IV);
/** SQL to encrypt */
const str = "SELECT * FROM user LIMIT 10 ;";
/** Encryption */
const encryptedValue = CryptoJS.AES.encrypt(str, key, {mode: CryptoJS.mode.CBC, iv: iv, padding: CryptoJS.pad.Pkcs7});
console.log(encryptedValue.toString());
</script>
</body>
</html>Backend
Whitelist IP check
/**
* @desc: IP check
* @return Response
*/
public function checkIp(): Response
{
if (!in_array($this->request->ip(), config('app.website_ip'))){
return response_json(0, 'success', ['is_success' => false]);
}
return response_json(0, 'success', ['is_success' => true]);
}SQL query tool management
Uses topthink/think-orm as the base query builder.
Backend decryption is performed with openssl_decrypt (AES‑128‑CBC).
/**
* @desc: SQL query tool management
* @return Response
*/
public function sqlQuery(): Response
{
try {
$whiteList = config('app.website_ip');
if (!in_array($this->request->ip(), $whiteList)) {
throw new NotFoundHttpException('404');
}
$cryptKey = 'yJFPEzGftZqNkwx';
$iv = '20243zyJFPEzhE0B';
$param = $this->request->post();
$result = $this->validate($param, QueryValidate::class);
if (true !== $result) {
throw new BadRequestHttpException('请输入SQL查询器编码');
}
// Decrypt the incoming SQL string
$param['query'] = openssl_decrypt($param['query'], 'AES-128-CBC', $cryptKey, 0, $iv);
$result2 = $this->validate($param, QueryValidate::class);
if (true !== $result2) {
throw new BadRequestHttpException('请输入SQL查询器编码');
}
// Disallow arbitrary DESCRIBE statements; enforce LIMIT clause
if (strstr(strtolower($param['query']), "describe") == false) {
preg_match('/limit (?:0|[1-9][0-9]?|100)$/i', $param['query'], $matchArr2);
if (empty($matchArr2)) {
throw new BadRequestHttpException('SQL查询器编码格式错误');
}
}
// Execute the validated query via ThinkORM
$queryResult = Db::connect(MYSQL_DRIVER)->query(trim($param['query']));
} catch (BaseException | \Exception $exception) {
throw new BadRequestHttpException('字典编码异常');
}
return response_json(0, '字典编码', $queryResult);
}Visualization
The tool enables developers in a private deployment to locate issues quickly, improve efficiency, and reduce security and communication overhead.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
Open Source Tech Hub
Sharing cutting-edge internet technologies and practical AI resources.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.