SQL查询中的单引号 [英] single quotes in SQL Query

问题描述

我正在编写一个用于更新数据库的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查询中的单引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！