在SQL查询中插入非常长的字符串-错误 [英] INSERTing very long string in an SQL query - ERROR

问题描述

问题是，每当我以php页面的形式输入大文本并尝试通过SQL查询将其输入数据库时​​，都会出现如下错误:

The problem is that whenever I input a big-ass text in the form of my php page and try to INSERT it through a SQL query into the database, it gives me an error like this:

您的SQL语法有误；请查看与您的MySQL服务器版本相对应的手册以获取正确的语法，以在'示例标题'，'花了漫长的雨季花了很短的时间关闭附近使用第1行的房间".

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 'sample title', 'spent the long months of the rainy season shut up in a small room th' at line 1"

查询是这样的:

$sql = "INSERT INTO `d` (`user_id`, `title`, `message`) VALUES ($user_id, '$topic', '$message')";

有趣的是，当我手动插入值并在phpmyadmin中运行查询时，它没有错误.

The interesting thing is that it gives me no error when I manually insert the values and run the query in phpmyadmin.

推荐答案

使用准备好的语句:

$db = new PDO(...);
$stmt = $db.prepare("INSERT INTO `d` (`user_id`, `title`, `message`) VALUES ($user_id, :topic, :message)");
// I'm assuming that title is a VARCHAR(50) and message is a VARCHAR(500)
$stmt->bindParam(":topic", $topic, PDO::PARAM_STR, 50);
$stmt->bindParam(":message", $topic, PDO::PARAM_STR, 500);
$stmt->execute();

这将转义/正确传递数据的工作直接交给了数据库引擎-数据库引擎.尽可能避免从用户输入中构建SQL字符串，这样可以避免许多潜在的SQL注入攻击.

This places the job of escaping / properly passing data squarely in the hands of the database engine - which is where it belongs. Avoid building SQL strings from user input wherever you can and you'll avoid many potential SQL injection attacks.

这篇关于在SQL查询中插入非常长的字符串-错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！