问题描述
我正在编写一个用于更新数据库的php脚本,但是当我尝试运行查询时却给出了错误,它会沿着
的行返回错误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 'id=15"' at line 1
在数据中存在空格后,要在附近使用"的地方似乎显示了查询的一部分.我假设我需要在php变量到查询数据的位置加上单引号,但是当我尝试将它们放入(甚至转义引号)时,我会从脚本中解析错误
SQL查询是
mysql_query("UPDATE Videos SET Title=".$_POST['Title'].", Preacher=".$_POST['Preacher'].", Date=".$_POST['Date'].", Service=".$_POST['Service'].", File=".$_POST['File'].", Description=".$_POST['Description']."WHERE id=".$_GET['vid_id']."\"") or die(mysql_error());
提前感谢您的帮助
您需要正确地对变量进行转义并将其用单引号引起来:
mysql_query("UPDATE
Videos
SET
Title = '".mysql_real_escape_string($_POST['Title'])."',
Preacher = '".mysql_real_escape_string($_POST['Preacher'])."',
Date = '".mysql_real_escape_string($_POST['Date'])."',
Service = '".mysql_real_escape_string($_POST['Service'])."',
File = '".mysql_real_escape_string($_POST['File'])."',
Description = '".mysql_real_escape_string($_POST['Description'])."'
WHERE
id = '".mysql_real_escape_string($_GET['vid_id'])."'")
or die(mysql_error());
没有正确地对变量进行转义,就会使自己容易受到 SQL注入攻击. /p>
编辑
为简化上述操作,您可以做一些技巧:
// Apply mysql_escape_string to every item in $_POST
array_map('mysql_real_escape_string', $_POST);
// Get rid of $_POST, $_POST['Title'] becomes $p_Title
extract($_POST, EXTR_PREFIX_ALL, 'p_');
// Use sprintf to build your query
$query = sprintf("UPDATE
Videos
SET
Title = '%s',
Preacher = '%s',
Date = '%s',
Service = '%s',
File = '%s',
Description = '%s'
WHERE
id = '%s'",
$p_Title,
$p_Preacher,
$p_Service,
$p_File,
$p_Description,
mysql_real_escape_string($_GET['vid_id']));
mysql_query($query) or die(mysql_error());
请注意,不建议将$ _POST和$ _GET变量混合使用.您应该通过表单中的隐藏输入字段提供更新ID.
Im writing a php script that is used to update a database but it is giving errors when i tries to run the query it returns an error along the lines of
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 'id=15"' at line 1
Where it says "To use near" seems to display part of the query after there is a space in the data. Im assuming i need to put single quotes around where the data to the query from the php variables but when i try to put them in (even escaping the quotes) i get parse errors from the script
The SQL Query is
mysql_query("UPDATE Videos SET Title=".$_POST['Title'].", Preacher=".$_POST['Preacher'].", Date=".$_POST['Date'].", Service=".$_POST['Service'].", File=".$_POST['File'].", Description=".$_POST['Description']."WHERE id=".$_GET['vid_id']."\"") or die(mysql_error());
Thank in advance for any help
You need to escape the variables properly and surround them by single quotes:
mysql_query("UPDATE
Videos
SET
Title = '".mysql_real_escape_string($_POST['Title'])."',
Preacher = '".mysql_real_escape_string($_POST['Preacher'])."',
Date = '".mysql_real_escape_string($_POST['Date'])."',
Service = '".mysql_real_escape_string($_POST['Service'])."',
File = '".mysql_real_escape_string($_POST['File'])."',
Description = '".mysql_real_escape_string($_POST['Description'])."'
WHERE
id = '".mysql_real_escape_string($_GET['vid_id'])."'")
or die(mysql_error());
Without escaping your variables properly, you are making yourself vulnerable to SQL injection attacks.
EDIT
To simplify the above, you can do a few tricks:
// Apply mysql_escape_string to every item in $_POST
array_map('mysql_real_escape_string', $_POST);
// Get rid of $_POST, $_POST['Title'] becomes $p_Title
extract($_POST, EXTR_PREFIX_ALL, 'p_');
// Use sprintf to build your query
$query = sprintf("UPDATE
Videos
SET
Title = '%s',
Preacher = '%s',
Date = '%s',
Service = '%s',
File = '%s',
Description = '%s'
WHERE
id = '%s'",
$p_Title,
$p_Preacher,
$p_Service,
$p_File,
$p_Description,
mysql_real_escape_string($_GET['vid_id']));
mysql_query($query) or die(mysql_error());
Note that mixing $_POST and $_GET variables is not encouraged. You should supply the update ID through an hidden input field in the form.
这篇关于SQL查询中的单引号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!