本文 PHP 后端已配套 Node.js 与 Python 版本，见页面中的对应实现；全部后端源码与运行说明。
由于阿里数据库（Oceanbase）完全兼容 MySQL，因此这里探讨的就是怎么连MySQL。
App Inventor 2 如何连接MySQL数据库
App Inventor 2 不能直接连接 MySQL（手机上的 App 不应该持有数据库账号密码），有两种做法：
- 不想自己维护服务器：用 CloudBase 拓展 的
CloudBaseMySQL组件，操作腾讯云开发托管的 MySQL，支持查询、插入、更新、删除和统计。 - 使用自己的 MySQL：用
Web客户端把查询请求 Post 到你自己的服务器，服务器上的 PHP、Node.js 或 Python 代码代理执行 SQL，本文讲的就是这种做法。
先看效果：
开发步骤
- 将 PHP 代码放在 Web 服务器上
- 在应用和 PHP 代码中设置 SQLKEY，它们必须匹配才能使此代码正常工作，以保障安全（注意：请不要使用特殊字符）
- 在 php 脚本中设置数据库连接信息
- 测试代码
注意事项：
- 返回 HTTP 代码 200 表示SELECT 查询成功
- 返回 HTTP 代码 201 表示不是有效的 SELECT 查询
- 返回 HTTP 代码 400，表示 SQL 查询错误
- 请注意SQL注入风险，加强php代码的检查逻辑
前端代码块
执行sql语句的代码块，参考如下：
服务器响应处理代码块，参考如下：
.aia源码下载：
后端php代码
php参考代码如下：
<?php
/*
* Written By: ShivalWolf
* Date: 2011/06/03
* Contact: Shivalwolf@domwolf.net
*
* UPDATE 2011/04/05
* The code now returns a real error message on a bad query with the mysql error number and its error message
* checks for magic_quotes being enabled and strips slashes if it is. Its best to disable magic quotes still.
* Checks to make sure the submitted form is a x-www-form-urlencode just so people dont screw with a browser access or atleast try to
* Forces the output filename to be JSON to conform with standards
*
* UPDATE 2011/06/03
* Code updated to use the Web Module instead of tinywebdb
*
* UPDATE 2013/12/26 and 2014/02/18
* minor modifications by Taifun, puravidaapps.com
*
* UPDATE 2014/07/11
* mysql API (deprecated) replaced by mysqli by Taifun
*
* UPDATE 2015/04/30
* SELECT logic adjusted (result stored in temp. file removed) by Taifun
*
* UPDATE 2016/02/21
* Bugfix Undefined variable: csv
*/
/************************************CONFIG****************************************/
//DATABSE DETAILS//
$DB_ADDRESS="";
$DB_USER="";
$DB_PASS="";
$DB_NAME="";
//SETTINGS//
//This code is something you set in the APP so random people cant use it.
$SQLKEY="secret";
/************************************CONFIG****************************************/
//these are just in case setting headers forcing it to always expire
header('Cache-Control: no-cache, must-revalidate');
error_log(print_r($_POST,TRUE));
if( isset($_POST['query']) && isset($_POST['key']) ){ //checks if the tag post is there and if its been a proper form post
//set content type to CSV (to be set here to be able to access this page also with a browser)
header('Content-type: text/csv');
if($_POST['key']==$SQLKEY){ //validates the SQL key
$query=urldecode($_POST['query']);
if(get_magic_quotes_gpc()){ //check if the worthless pile of crap magic quotes is enabled and if it is, strip the slashes from the query
$query=stripslashes($query);
}
$conn = new mysqli($DB_ADDRESS,$DB_USER,$DB_PASS,$DB_NAME); //connect
if($conn->connect_error){ //checks connection
header("HTTP/1.0 400 Bad Request");
echo "ERROR Database Connection Failed: " . $conn->connect_error, E_USER_ERROR; //reports a DB connection failure
} else {
$result=$conn->query($query); //runs the posted query
if($result === false){
header("HTTP/1.0 400 Bad Request"); //sends back a bad request error
echo "Wrong SQL: " . $query . " Error: " . $conn->error, E_USER_ERROR; //errors if the query is bad and spits the error back to the client
} else {
if (strlen(stristr($query,"SELECT"))>0) { //tests if it's a SELECT statement
$csv = ''; // bug fix Undefined variable: csv
while ($fieldinfo = $result->fetch_field()) {
$csv .= $fieldinfo->name.",";
}
$csv = rtrim($csv, ",")."\n";
echo $csv; //prints header row
$csv = '';
$result->data_seek(0);
while($row = $result->fetch_assoc()){
foreach ($row as $key => $value) {
$csv .= $value.",";
}
$csv = rtrim($csv, ",")."\n";
}
echo $csv; //prints all data rows
} else {
header("HTTP/1.0 201 Rows");
echo "AFFECTED ROWS: " . $conn->affected_rows; //if the query is anything but a SELECT, it will return the number of affected rows
}
}
$conn->close(); //closes the DB
}
} else {
header("HTTP/1.0 400 Bad Request");
echo "Bad Request"; //reports if the secret key was bad
}
} else {
header("HTTP/1.0 400 Bad Request");
echo "Bad Request";
}
?>
Node.js 与 Python 后端
两个版本都接收 POST /mysql.php，表单字段仍为 key、query：SELECT 返回 HTTP 200 和含表头的 CSV；写入返回 HTTP 201 和 AFFECTED ROWS: 数量；请求或 SQL 错误返回 HTTP 400。CSV 中的逗号、双引号和换行会正确转义，可交给 Web 客户端的 CSV 积木解析。把原请求网址替换为 http://服务器IP:3000/mysql.php，其余字段保持一致。
先在启动脚本的终端设置连接信息；Windows 可用 PowerShell 的 $env:DB_HOST="127.0.0.1" 等同名环境变量：
export DB_HOST=127.0.0.1 DB_PORT=3306 DB_NAME=你的数据库
export DB_USER=你的数据库用户 DB_PASS=你的数据库密码 SQLKEY=与你的App一致的密钥
这仍是执行客户端 SQL 的教学接口，SQLKEY 不能限制 SQL 权限；请使用受限数据库账号。正式业务应改为固定操作和参数化查询。
Node.js 对应实现
保存为 mysql-query.cjs，在文件所在目录安装依赖并启动：
npm install express@5 mysql2
node mysql-query.cjs
// npm install express@5 mysql2
const express = require('express');
const mysql = require('mysql2/promise');
const app = express();
const pool = mysql.createPool({host: process.env.DB_HOST || '127.0.0.1', port: Number(process.env.DB_PORT || 3306),
user: process.env.DB_USER, password: process.env.DB_PASS, database: process.env.DB_NAME, charset: 'utf8mb4'});
app.use(express.urlencoded({extended: false}));
app.post('/mysql.php', async (req, res) => {
res.set('Cache-Control', 'no-cache, must-revalidate').type('text/csv');
const {key, query} = req.body || {};
if (!process.env.SQLKEY || key !== process.env.SQLKEY || typeof query !== 'string') return res.status(400).send('Bad Request');
try {
// 表单解析已经 URL 解码一次，不能再 decodeURIComponent，否则 SQL 中的 +、% 会损坏。
const [rows, fields] = await pool.query(query);
if (!Array.isArray(rows)) return res.status(201).send('AFFECTED ROWS: ' + rows.affectedRows);
const csv = value => /[",\r\n]/.test(String(value ?? '')) ? '"' + String(value ?? '').replace(/"/g, '""') + '"' : String(value ?? '');
const lines = [fields.map(field => csv(field.name)).join(',')];
for (const row of rows) lines.push(fields.map(field => csv(row[field.name])).join(','));
res.send(lines.join('\n') + '\n');
} catch (error) { res.status(400).send('ERROR SQL query failed'); }
});
if (require.main === module) app.listen(Number(process.env.PORT || 3000), '0.0.0.0');
module.exports = {app, pool};
Python 对应实现
保存为 mysql_query.py，在文件所在目录安装依赖并启动：
python3 -m pip install Flask PyMySQL
python3 mysql_query.py
# python3 -m pip install Flask PyMySQL
import csv
import io
import os
import pymysql
from flask import Flask, request, Response
app = Flask(__name__)
def connect():
return pymysql.connect(host=os.environ.get('DB_HOST', '127.0.0.1'), port=int(os.environ.get('DB_PORT', 3306)),
user=os.environ['DB_USER'], password=os.environ['DB_PASS'], database=os.environ['DB_NAME'],
charset='utf8mb4', autocommit=True)
def reply(text, status=200):
return Response(text, status=status, mimetype='text/csv', headers={'Cache-Control': 'no-cache, must-revalidate'})
@app.post('/mysql.php')
def query():
if not os.environ.get('SQLKEY') or request.form.get('key') != os.environ['SQLKEY'] or 'query' not in request.form:
return reply('Bad Request', 400)
connection = None
try:
connection = connect()
with connection.cursor() as cursor:
cursor.execute(request.form['query']) # 表单只解码一次
if cursor.description is None: return reply(f'AFFECTED ROWS: {cursor.rowcount}', 201)
output = io.StringIO(newline='')
writer = csv.writer(output, lineterminator='\n')
writer.writerow([field[0] for field in cursor.description])
writer.writerows(cursor.fetchall())
return reply(output.getvalue())
except (pymysql.MySQLError, KeyError):
return reply('ERROR SQL query failed', 400)
finally:
if connection is not None: connection.close()
if __name__ == '__main__':
app.run(host='0.0.0.0', port=int(os.environ.get('PORT', 3000)))
扫码添加客服咨询
