How to resolve "mysql Proxy Warning - Syntax Forbidden prepare"?

[Q5EbA7Vdyw84efER]

I'm using Nextjs framework, and wrote my code as a function as below

const con = await getConnection()
try {
  await con.execute(`UPDATE tool set category_id=?,name=?,description=?,url=?,draw_content=? where id=?`, [category_id, name, description, url, draw_content, id])
} catch (error) {
  throw error
}

Everything is OK in test environment, but in our company's production MYSQL environment, it didn't work and output such log error

Error: Proxy Warning - Syntax Forbidden Prepare:UPDATE tool set category_id=?,name=?,description=?,url=?,draw_content=? where id=?
at u.execute (/.next/server/chunks/572.js:10:55409)
at g (/.next/server/chunks/764.js:1:7427)
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
at async /node_modules/next/dist/compiled/next-server/app-page.runtime.prod.js:16:406
at async rm (/node_modules/next/dist/compiled/next-server/app-page.runtime.prod.js:15:6342)
at async rq (/node_modules/next/dist/compiled/next-server/app-page.runtime.prod.js:18:1249)
at async doRender (/node_modules/next/dist/server/base-server.js:1376:30)
at async cacheEntry.responseCache.get.routeKind (/node_modules/next/dist/server/base-server.js:1537:28)
at async NextNodeServer.renderToResponseWithComponentsImpl (/node_modules/next/dist/server/base-server.js:1445:28)
at async NextNodeServer.renderPageComponent (/node_modules/next/dist/server/base-server.js:1842:24) {
code: 'ER_UNKNOWN_ERROR',
errno: 1105,
sql: 'UPDATE tool set category_id=?,name=?,description=?,url=?,draw_content=? where id=?',
sqlState: '07000',
sqlMessage: 'Proxy Warning - Syntax Forbidden Prepare:UPDATE tool set category_id=?,name=?,description=?,url=?,draw_content=? where id=?'

[sidorares]

hi @Q5EbA7Vdyw84efER what is your mysql server?

[Q5EbA7Vdyw84efER]

hi @Q5EbA7Vdyw84efER what is your mysql server?

It's a MYSQL service bind a proxy, which port is not 3306, now I've got it worked by changing the .execute() to .query(), refered to your post in 2016 #476 (comment).
However I'm still kind of confused of the intrinsic reason.

[sidorares]

The reason is that query passed to PREPARE command has syntax error, though at a first glance I can't see where can it be. Can you connect with command line client to your server and try to make PREPARE command manually? The syntax is described here - https://dev.mysql.com/doc/refman/8.0/en/prepare.html

So it'll probably be something like PREPARE updateStmt FROM 'UPDATE tool set category_id=?,name=?,description=?,url=?,draw_content=? where id=?'

[Q5EbA7Vdyw84efER]

The reason is that query passed to PREPARE command has syntax error, though at a first glance I can't see where can it be. Can you connect with command line client to your server and try to make PREPARE command manually? The syntax is described here - https://dev.mysql.com/doc/refman/8.0/en/prepare.html

So it'll probably be something like PREPARE updateStmt FROM 'UPDATE tool set category_id=?,name=?,description=?,url=?,draw_content=? where id=?'

I did several tests in both direction connection to MYSQL and connection to a proxy(MYSQL is behind the proxy, this will bring some problem)

1.The prepare SQL in direction connection environment shows as below
mysql> SELECT VERSION()
-> ;
+-----------+
| VERSION() |
+-----------+
| 8.0.25 |
+-----------+
1 row in set (0.05 sec)

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
Query OK, 0 rows affected (0.05 sec)
Statement prepared

mysql> SET @A = 3;
Query OK, 0 rows affected (0.05 sec)

mysql> SET @b = 4;
Query OK, 0 rows affected (0.05 sec)

mysql> EXECUTE stmt1 USING @A, @b;
+------------+
| hypotenuse |
+------------+
| 5 |
+------------+
1 row in set (0.05 sec)

2.proxy environment, I can't connect it successfully by cli tool(which always prompted wrong password), so I switched to the Jetbrain Datagrip tool, but driver MYSQL in Datagrip also failed(prompted "[08001] CLIENT_PLUGIN_AUTH is required"), then I tried some workaround and found that driver MYSQL5.1 in Datagrip can get it connected. Then I tried the same prepare SQL in direction connection test.
PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
But just the first command triggered a wrong message in the lower right corner in Datagrip, showed as below.
Illegal mix of collations (utf8mb4_bin,NONE) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='.

3.I also tried using Datagrip tool in direction connect environment, input below commands and it worked successfully, I got the result 5, sqrt(pow(3,2)+pow(4,2))=5, the output record is below
scpfdb> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'
[2024-04-01 16:49:10] completed in 109 ms
scpfdb> SET @A = 3
[2024-04-01 16:49:10] completed in 106 ms
scpfdb> SET @b = 4
[2024-04-01 16:49:10] completed in 106 ms
scpfdb> EXECUTE stmt1 USING @A, @b
[2024-04-01 16:49:11] 1 row retrieved starting from 1 in 120 ms (execution: 108 ms, fetching: 12 ms)

4.I tried another time to the proxy using Datagrip, I found that the lower right corner collation popup is only a warning, which didn't blocked the command, so the first command PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; had executed successfully, but the second command SET @A = 3; failed and told me 'Proxy Warning - Syntax Forbidden', the intact output record is below.
scpfdb> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'
[2024-04-01 16:43:00] completed in 213 ms
scpfdb> SET @A = 3
[2024-04-01 16:43:04] [07000][1105] Proxy Warning - Syntax Forbidden Query:/* ApplicationName=DataGrip 2023.3.4 / SET @A = 3
scpfdb> SET @b = 4
[2024-04-01 16:43:14] [07000][1105] Proxy Warning - Syntax Forbidden Query:/ ApplicationName=DataGrip 2023.3.4 */ SET @b = 4
scpfdb> EXECUTE stmt1 USING @A, @b
[2024-04-01 16:43:16] 1 row retrieved starting from 1 in 247 ms (execution: 213 ms, fetching: 34 ms)

I asked the DBA workmate and she said that the proxy environment doesn't support such SQL.

[sidorares]

Just a guess, but looks like proxy server tries to parse sql to be able to do some routing logic and for some reason is unable to parse the query you pass to it

[Q5EbA7Vdyw84efER]

Just a guess, but looks like proxy server tries to parse sql to be able to do some routing logic and for some reason is unable to parse the query you pass to it

Maybe, but whatever I will and have to use .query() function in the later coding and continously be deep into the heavy work of my company and from my boss.