MySQL 事务语法错误

发布于 2024-12-15 13:51:33 字数 790 浏览 1 评论 0原文

以下抛出语法错误。整个查询在带有文字的 MySQL 客户端中运行，但在从 PHP 传递时会崩溃。在 PHP 中，如果单独提交，每个查询都有效，但如果在 START 和 COMMIT 内提交，则无效。

错误：

1064: You have an error in your SQL syntax; check the manual ...etc...right
syntax to use near 'INSERT INTO user_degrees (user_id, degree_id, acadcat_id
, inst_id) VALUES (' at line 2

查询：

$query="START TRANSACTION;

INSERT INTO user_degrees (user_id, degree_id, acadcat_id, inst_id) 
VALUES ((SELECT id FROM users WHERE users.username = '$user')
   , '$degreeid', '$studyfocus', '$institution');

UPDATE users 
SET degree".$dnum." = (SELECT LAST_INSERT_ID())
WHERE username = '$user';

COMMIT;";

所有 $var 都是类属性并传递整数，但 $user 除外，它传递用户名会话变量。 $dnum 用于更改类实例之间的列名称，我可能在 MySQL 中错误地连接它。

原文

The following is throwing a syntax error. The entire query works in a MySQL client with literals, but breaks down when passing from PHP. In PHP, each query works if submitted separately, but not if within START and COMMIT.

The error:

1064: You have an error in your SQL syntax; check the manual ...etc...right
syntax to use near 'INSERT INTO user_degrees (user_id, degree_id, acadcat_id
, inst_id) VALUES (' at line 2

Query:

$query="START TRANSACTION;

INSERT INTO user_degrees (user_id, degree_id, acadcat_id, inst_id) 
VALUES ((SELECT id FROM users WHERE users.username = '$user')
   , '$degreeid', '$studyfocus', '$institution');

UPDATE users 
SET degree".$dnum." = (SELECT LAST_INSERT_ID())
WHERE username = '$user';

COMMIT;";

All the $vars are class properties and pass integers, except for $user, which passes a username session variable. $dnum is used to change column names between instances of the class and I might be concatenating it incorrectly within MySQL.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

孤独难免 2024-12-22 13:51:33

作为安全措施，PHP 的 mysql 驱动程序仅允许每个 mysql_query() 调用执行一次查询。您必须发出多个单独的查询：

$result = mysql_query("START TRANSACTION");
$result = mysql_query("INSERT ...");
$result = mysql_query("UPDATE ...");
$result = mysql_query("COMMIT;");

...在每个阶段进行适当的检查，以确保查询不会失败（我在这里省略了）。

请注意，此安全措施仅适用于顶级查询。每次调用一一顶级查询。您可以根据需要拥有任意数量的子查询。

PHP's mysql driver only allows a single query per mysql_query() call as a security measure. You'll have to issue multiple separate queries:

$result = mysql_query("START TRANSACTION");
$result = mysql_query("INSERT ...");
$result = mysql_query("UPDATE ...");
$result = mysql_query("COMMIT;");

... with appropriate checking at each stage to make sure the query didn't fail (which I've omitted from here).

Note that this security measure only applies to top-level queries. One one top-level query per call. You can have as many subqueries as you want/need.

回复收藏 0 原文

~没有更多了~