使用撇号时的SQL错误 [英] SQL Error when using apostrophes

问题描述

每当我尝试发布带有撇号的内容时，都会出现以下错误:

I'm getting the following error whenever I try to post something with an apostrophe in it:

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...

例如，当我尝试发布/使用INSERT之类的内容时，例如我正在努力".这让我出错了.如果我写我正在努力"，那一切都是好事.

For example when I'm trying to post/using INSERT something like "I'm working hard".It's getting me an error. If I write "I am working hard" everything is fiine.

代码是:

 $sql="INSERT INTO tb_table (`postcontent`, `userid`, `posttime`) VALUES ('".$_POST[content]."', '".$user_id."', '".time()."')";

有什么解决办法吗?

推荐答案

这是因为您使用单引号来显示MySQL中该字段的每个值的开始和结束位置.如果在字符串的中间放置撇号，数据库突然会认为您正在尝试在三个表字段中放入四个值，或者类似的东西.

That's because you are using apostrophes to show MySQL where each value for the field starts and ends. If you put an apostrophe in the middle of the string, suddenly the database thinks that you're trying to put in four values in three table fields, or some such thing.

您似乎正在使用PHP在数据库中插入数据，因此我将为您提供一些示例，以使用PHP提供的方法进行处理.

It looks like you're using PHP to insert data in the database so I'll give you a couple of examples of dealing with this with the means that PHP provides.

使用 mysql_real_escape_string()修复此问题的快速方法:

A quick way to fix it to use mysql_real_escape_string():

$sql="INSERT INTO tb_table (`postcontent`, `userid`, `posttime`) 
         VALUES ('" . mysql_real_escape_string($_POST['content']) . "',
                 '" . mysql_real_escape_string($user_id) . "', 
                 ".time().")";

一种更好的方法是使用准备的语句:

A better approach would be to use prepared statements:

$db = // your mysqli() connection

$sql="INSERT INTO tb_table (`postcontent`, `userid`, `posttime`) 
             VALUES (?, ?, ?)";

if ($stmt = $db->prepare($sql)) 
{
   $stmt->bind_param("ssi", $_POST['content'], $user_id, time());
   $stmt->execute();           
   $stmt->close();
}

P.S.您不需要在time()周围加上单引号-这是一个数字，可以按原样插入.

P.S. You don't need single quotes around time() - this is a number, it's safe to insert as is.

这篇关于使用撇号时的SQL错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！