语法错误或访问违背:1064 ' brandname。

I'm getting this error in my Magento script:

我在我的Magento脚本中得到这个错误:

Product not added exception:exception 'PDOException' with message

产品未添加异常:异常“PDOException”与消息。

'SQLSTATE[42000]: Syntax error or access violation: 1064 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 's Secret'' at line 1'

'SQLSTATE[42000]:语法错误或访问违反:1064在SQL语法中有错误;请检查与MySQL服务器版本对应的手册，以便在第1行中使用near 's Secret的语法。

Some background info:

一些背景信息:

I have a PHP script running on a cron job to add and update products. It runs a while now, but I got just now this error. I think it's because the manufacturers name got an apostrophe in it. But I have no clue how to fix it.

我有一个PHP脚本运行在cron作业上，以添加和更新产品。它运行了一段时间，但是现在我得到了这个错误。我想这是因为制造商的名字里有一个撇号。但是我不知道怎么修理它。

Changing the manufacturer's name is not a option.

更改制造商的名称不是一个选项。

function addManufacture($pid,$men){
    $resource = Mage::getSingleton('core/resource');
    $readConnection = $resource->getConnection('core_read');
    $query = "SELECT manufacturers_id FROM p1_manufacturers WHERE m_name='".$men."'";
    $lastid = $readConnection->fetchOne($query); 
    $write = Mage::getSingleton("core/resource")->getConnection("core_write");
    if($lastid){}else{
    $url = createUrl($men); 
    $query = "insert into p1_manufacturers (m_name,identifier,status) values ('".$men."','".$url."',1)";
    $write->query($query);
    $lastid = $write->lastInsertId();
    }
    $query1 = "insert into p1_manufacturers_products (manufacturers_id,product_id) values ('".$lastid."','".$pid."')";
    $write->query($query1);

    $query3 = "SELECT manufacturers_id FROM p1_manufacturers_store WHERE manufacturers_id='".$lastid."'";
    $mid = $readConnection->fetchOne($query3); 
    if($mid){} else {
    $query2 = "insert into p1_manufacturers_store (manufacturers_id,store_id) values ('".$lastid."',0)";
    $write->query($query2);
    }

}

2 个解决方案

#1

Here is the problem:

这是问题:

$query = "SELECT manufacturers_id FROM p1_manufacturers WHERE m_name='".$men."'";

Replace that with:

,替换为:

$menEscaped = mysql_real_escape_string($men);
$query = "SELECT manufacturers_id FROM p1_manufacturers WHERE m_name='".$menEscaped."'";

For readability, I might be inclined to reformat it thus:

对于可读性，我可能倾向于重新格式化它:

$menEscaped = mysql_real_escape_string($men);
$query = "
    SELECT
        manufacturers_id
    FROM
        p1_manufacturers
    WHERE
        m_name='{$menEscaped}'
";

The problem is you are not escaping your input variables, and if this comes from user input, you may find people injecting SQL of their own choice into your database. And that's generally not good!

问题是，您并不是在逃避输入变量，如果这来自用户输入，您可能会发现人们将自己选择的SQL注入到您的数据库中。这通常不好!

Addendum: the above may work, but I've just spotted you are using a library called Mage. This being the case, you will need to find out how to escape strings using that library - it will be something like $write->escapeString($men).

附录:上面的内容可能有用，但我刚刚发现您正在使用一个名为Mage的库。在这种情况下，您需要了解如何使用该库来摆脱字符串——它将会是$write->escapeString($men)。

As has been noted in the comments, it is even better if you can switch to paramerisation. You'll need to check if your library supports that.

正如在评论中指出的那样，如果你能切换到paramerisation，那就更好了。您需要检查您的库是否支持它。

#2

Your problem is being caused by an unescaped single-quote appearing in your data, and creating a syntax error in the queries you are submitting to your database.

您的问题是由于在您的数据中出现了一个未转义的单引号，并且在您提交给数据库的查询中创建了语法错误。

Unfortunately, your database access code is hidden in some class, so it's not immediately obvious what changes are required. However...

不幸的是，您的数据库访问代码隐藏在某个类中，因此不立即明显地看出需要进行哪些更改。然而……

As an absolute minimum you should escape any user data before applying it to the database. For this function this means

作为绝对最小值，在将用户数据应用到数据库之前，应该避免使用任何用户数据。对于这个函数，这意味着。

$men = mysql_real_escape_string($men);
$pid = mysql_real_escape_string($pid);

added at the top of the function. I have assume you are using 'mysql()` in this code.

添加到函数的顶部。我假设您在这段代码中使用了“mysql()”。

Watch the line $url = createUrl($men); as this will be affected by this change. You may need further modifications here, and createUrl() may need to be changed too.

查看line $url = createUrl($men);因为这将受到这种变化的影响。您可能需要进一步修改，createUrl()可能也需要更改。

You will need to make similar changes in every function that accesses your database.

您需要在每个访问数据库的函数中进行类似的更改。

If you are using mysqli() more work will be required as the arguments are different and this 'easy' fix won't work.

如果您正在使用mysqli()，则需要更多的工作，因为参数是不同的，而且这个“简单”的修复不会起作用。

Ultimately you should look to rewrite your code to use prepared statements.

最后，您应该考虑重写代码以使用准备好的语句。

Your code is seriously vulnerable to attack. There is a lot of work here. Get to it!

您的代码非常容易受到攻击。这里有很多工作。去做吧!

Edit Thanks to @halfer for spotting the use of Mage. Magento uses the Zend framework which in turn uses PDO objects. Delving into the code you can rewrite the functions to use prepared statements which will deal with your problem effectively. This answer has a fuller description. This is a better fix than I suggested above, but you still have a great deal of work to do.

编辑感谢@halfer发现法师的使用。Magento使用了Zend框架，该框架反过来使用PDO对象。深入到代码中，您可以重写这些函数来使用准备好的语句，这些语句将有效地处理您的问题。这个答案有更完整的描述。这比我上面提到的要好，但你还有很多工作要做。

#1