错误:在SQL语法中有错误;请检查与MySQL服务器版本对应的手册，以便使用正确的语法。

The code below is used when the user enters a youtube url it get the youtube id from the url. It then get the title for that video with that id. That is then inserted into a database and recalled to display the image of the video associated with that id.

当用户输入youtube url时，将使用下面的代码，从url获取youtube id。然后，它就会用那个id获得那个视频的标题，然后插入到一个数据库中，然后被召回来显示与那个id相关联的视频的图像。

if i use this youtube url http://www.youtube.com/watch?v=p64tAbP-nHE or and other youtube url. If the title of that youtube url contains a ' ie(2013 Ravens Rock Rally - Jonathan O'Callaghan & Gavin Sheehan - Stage 3) i get the error

如果我使用这个youtube网址http://www.youtube.com/watch?v=p64tAbP-nHE或其他youtube url。如果youtube url的标题包含了一个“ie(2013年的Ravens Rock Rally——Jonathan O' callaghan & Gavin Sheehan - Stage 3)，我就会犯错误。”

Error: 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 'Callaghan & Gavin Sheehan - Stage 3'' at line 1

错误:在SQL语法中有错误;请检查与MySQL服务器版本对应的手册，以便在第1行使用“Callaghan & Gavin Sheehan - Stage 3”。

Any help would be great, thanks in advance.

任何帮助都将是伟大的，谢谢提前。

Here is my code:

这是我的代码:

<?php
include 'dataconnection.php';
// Check connection
if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
    else        
        $url = $_POST['set_video'];
        parse_str( parse_url( $url, PHP_URL_QUERY ), $my_array_of_vars );

        $youtube_id = $my_array_of_vars['v'];

        $info = $_POST['set_desc'];

        $id = $my_array_of_vars['v'];
        $xmlData =  simplexml_load_string(file_get_contents("http://gdata.youtube.com/feeds/api/videos/{$id}?fields=title"));

        $title = (string)$xmlData->title;

        $sql="INSERT INTO videodetails SET id='null',youtube_id='$youtube_id',info='$title'";

        if (!mysqli_query($connection,$sql))
          {
          die('Error: ' . mysqli_error($connection)); 
          }
        echo "<div id='pageheader'>
                1 record added<span id='logout'>Return to <a href='contributors_login.html'>Contributors Login</a></span>  
             </div>";

        echo '<div id="setvideo"><a href="http://www.youtube.com/watch?v='.$my_array_of_vars['v'].'" target="_blank"><img src="http://i4.ytimg.com/vi/'.$my_array_of_vars['v'].'/default.jpg" style="border:solid 2px white;"></a><p>'.$title.'</p></div>';

    mysqli_close($connection);  
?>

4 个解决方案

#1

Your yourTube name has a quote in it, so the SQL line

您的yourTube名称有一个引号，因此SQL行。

$sql="INSERT INTO videodetails SET id='null',youtube_id='$youtube_id',info='$title'

becomes this

就这

INSERT INTO videodetails SET id='null',
   youtube_id='2013 Ravens Rock Rally - Jonathan O'Callaghan & Gavin Sheehan - Stage 3'

which MySQL sees as

MySQL认为

INSERT INTO videodetails SET id='null',
   youtube_id='2013 Ravens Rock Rally - Jonathan O',Callaghan & Gavin Sheehan - Stage 3'

and MySQL doesn't understand Callaghan & Gavin Sheehan - Stage 3'

而MySQL不理解Callaghan & Gavin Sheehan - Stage 3'

#2

Use mysqli_real_escape_string in your INSERT INTO ... part.

在INSERT中使用mysqli_real_escape_string…部分。

You open single quotes. But the title contains also single quotes so they get closed. MySQL doesn't know this and thinks the text that follows is a MySQL keyword.

你打开单引号。但标题也包含单引号，所以它们会被关闭。MySQL不知道这一点，认为下面的文本是一个MySQL关键字。

#3

The case of strings that contain quotes is why mysqli_real_escape_string() exists, to find those quotes and insert a \ before them so they count as literal quote characters, instead of terminating the quoted string.

包含引号的字符串的情况是为什么mysqli_real_escape_string()存在，以查找这些引号并在它们前面插入一个\，因此它们以文字引用字符计数，而不是终止引用的字符串。

. . .
$youtube_id = mysqli_real_escape_string($my_array_of_vars['v']);
$info = mysqli_real_escape_string($connection, $_POST['set_desc']);

$sql="INSERT INTO videodetails SET id='null',youtube_id='$youtube_id',info='$title'";

if (!mysqli_query($connection,$sql))
. . .

But the best practice is to use query parameters, so you don't need to worry about those embedded quotes. Any place you have a variable in your SQL string in place of a literal value, use a query parameter placeholder. These placeholders don't work in place of table names, column names, or SQL expressions or keywords -- they only work where you would normally put a single scalar value in your SQL.

但是最好的做法是使用查询参数，所以您不必担心那些嵌入的引号。任何地方的SQL字符串中都有一个变量，使用一个查询参数占位符。这些占位符不能代替表名、列名或SQL表达式或关键字——它们只工作于通常在SQL中放置单个标量值的地方。

$sql="INSERT INTO videodetails SET id='null',youtube_id=?,info=?";
if ($stmt = mysqli_prepare($connection, $sql)) {
    mysqli_stmt_bind_param($stmt, 'ss', $youtube_id, $title);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);
}

This is safer, and makes your SQL more readable. Notice that the ? placeholder itself doesn't go inside quotes, even if the value you bind to it is a string.

这样更安全，也使您的SQL更具可读性。注意到吗?占位符本身不会进入引号，即使绑定到它的值是字符串。

PS: I question your use of the quoted string 'null' where you may mean the SQL keyword NULL.

PS:我怀疑您使用的引用字符串“null”，您可能指的是SQL关键字null。

#4

Your insert query is not valid sql. The keyword "set" is used with update queries. Insert queries look like this:

您的插入查询不是有效的sql。关键字“set”用于更新查询。插入查询如下:

insert into atable
(f1, f2, etc)
values
(val1, val2, etc)

or this

或者这个

insert into atable
(f1, f2, etc)
select val1, val2, etc
from someOtherTables

#1