我可以在SQL查询中使用htmlentities()吗? [英] Can I use htmlentities() in an SQL query?

问题描述

非常感谢您提出我最初的问题所进行的讨论.我接受了杰伊(Jay)的建议使用bind_param()，但有一些我不了解的内容可能是服务器错误:该网站在检索...时遇到错误".我不知道示例附带的参数'sssd'是什么意思.

Much thanks for the discussion my original question generated. I took Jay's suggestion to use bind_param(), but there is something I don't understand about it that may be giving me the Server Error: "The website encountered an error while retrieving...". I don't know what the parameter 'sssd' that came with the example means.

对于引起服务器错误的任何建议，我们深表感谢.

Any suggestions as to what is generating the Server Error are much appreciated.

<?php

$mysqli = new mysqli('my-database-address', 'my-username', 'my-password', 'my-database-name');
f (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit(); }

$stmt = $mysqli->prepare("INSERT INTO volunteers VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$stmt->bind_param('sssd', $first_name, $last_name, $street_address, $apt_unit, $city, $zip, 
$email, $phone, $planning, $signatures, $canvassing, $phone_bank, $media, $press_releases,
$volunteer_coordinator, $speaker, $house_parties, $web_page, $other, $skills, $organizations);

$first_name = '$_POST[first_name]'; $last_name = '$_POST[last_name]'; $street_address = '$_POST[street_address]';
$apt_unit = '$_POST[apt_unit]'; $city = '$_POST[city]'; $zip = '$_POST[zip]'; $email = '$_POST[email]';
$phone = '$_POST[phone]'; $planning = '$_POST[planning]'; $signatures = '$_POST[signatures]'; 
$canvassing = '$_POST[canvassing]'; $phone_bank = '$_POST[phone_bank]'; $media = '$_POST[media]'; 
$press_releases = '$_POST[press_releases]'; $volunteer_coordinator = '$_POST[volunteer_coordinator]'; 
$speaker = '$_POST[speaker]'; $house_parties = '$_POST[house_parties]'; $web_page = '$_POST[web_page]'; 
$other = '$_POST[other]'; $skills = '$_POST[skills]'; $organizations = '$_POST[organizations]';

$stmt->execute();
$stmt->close();

echo "<br /><br />";
echo "<div class='center-col-wrap'>";
echo "Your information has been received.";
echo "<br /><br />";
echo "Thank you for volunteering!"; echo "<br />";
echo "Your help in this effort to bring greater democracy to Oakland"; echo "<br />";
echo "will go a long way to create a healthy and informed community."; echo "<br />";
echo "<br /><br />";
echo "<a href='http://communitydemocracyproject.org/'>Return to CDP Home Page.</a>";
echo "</div>";

$mysqli->close();
?>

我的原始问题如下:

我不知道这是否行得通.不能，那么如何在这里使用htmlentities()?

I didn't know if this would work or not. It does not So how can I use htmlentities() here?

非常感谢您的帮助.

$sql="INSERT INTO volunteers (first_name, last_name, street_address, apt_unit, city, zip, email, phone,
planning, signatures, canvassing, phone_bank, media, press_releases, volunteer_coordinator, speaker, 
house_parties, web_page, other, skills, organizations)
VALUES
('htmlentities($_POST[first_name])','htmlentities($_POST[last_name])','htmlentities($_POST[street_address])',
'htmlentities($_POST[apt_unit])','htmlentities($_POST[city])','htmlentities($_POST[zip])',
'htmlentities($_POST[email])','htmlentities($_POST[phone])','$_POST[planning]','$_POST[signatures]','$_POST[canvassing]','$_POST[phone_bank]',
'$_POST[media]','$_POST[press_releases]','$_POST[volunteer_coordinator]','$_POST[speaker]',
'$_POST[house_parties]','$_POST[web_page]','$_POST[other]','htmlentities($_POST[skills])','htmlentities($_POST[organizations])')";

推荐答案

正如西奥多(Theodore)所说，从不不要使用htmlentities()来转义"要放入数据库中的内容.

As Theodore said, never use htmlentities() for "escaping" something you want to put in your DB.

当任何(可能)来自外部(用户)的信息存储在数据库中时，我强烈建议使用准备好的语句.

I strongly recommend to use prepared statements when anything that (could) come from outside (the user) is stored in a database.

准备好的语句真的很容易使用.

Prepared statements are really easy to use.

如果您使用PDO来访问数据库，则可以在此处. 如您所见，bindParam()方法用于将任何值分配给查询中的占位符.

If you use PDO to access you database help can be found here. As you can see the bindParam() method is used to assign any value to a placeholder in the query.

如果您使用mysqli，则可以在此处. bind_param()的语法略有不同，因为占位符没有名称(顺序问题)，并且第一个参数是一个字符串，该字符串确定参数具有的类型(字符串为"s"，整数为"i"，依此类推)

If you use mysqli you can find the docs here. The syntax of bind_param() is slightly different since the placeholders don't have names (order matters) and first argument is a string that determines what type the arguments have ("s" for string, "i" for integer and so on).

使用准备好的语句有几个积极的作用.首先，它会自动掩盖bindParam()/bind_param()方法中提供的数据，这是关闭

Using prepared statements has several positive effects. First of all it automatically masks the data that is provided in the bindParam()/bind_param() method and is the best way to close the SQL injection attack vector and it even optimizes the performance of your queries by storing the execution plan in the database (this has a little overhead but if you execute a query twice it pays off double).

PS:htmlentities()仅在要将某些HTML作为原始文本显示给用户(例如代码清单)时才应使用.

PS: htmlentities() should only be used if you want to display some HTML as raw text to your users (code listings for instance).

PPS:由于不安全，请不要使用real_escape_string()来防止SQL注入(

PPS: Don't use real_escape_string() to prevent SQL injection since it's not safe (supeskt.org)

首先，要进行跟进，您应该提出一个新问题.人们不会阅读已经被标记为已回答的问题，而通过打开新问题，您可以为善良的人们提供获得奖励的机会. :)

First of all, for a follow up you should ask a new question. People don't read questions that are already marked as answered and by opening new questions you give kind people an opportunity to get some reward. :)

尽管如此，第一个参数"sssd"告诉数据库提供者，您正在传递四个参数，三个参数为string类型，第四个参数为double类型(在文档中的示例中，三个字符串和一个double类型是绑定的("DEU" ，巴伐利亚"，"F"和11.2)).显然这里不是这种情况，您实际上正在传递(绑定)21个值.

Nevertheless, the first argument "sssd" tells the database provider that you are passing four arguments, three of type string and a fourth of type double (in the example in the docs three strings and one double are bound ("DEU", "Bavarian", "F" and 11.2)). This is obviously not the case here, you are actually passing (binding) 21 values.

根据volunteers表中列的类型，您需要传递21个字符的字符串作为第一个参数. 有四种可能的字符可用于确定类型:

Depending of the type that the columns in your volunteers table have you need to pass a string of 21 characters as the first argument. There are four possible chars that can be used to determine the type:

• i为整数
• d表示双精度(浮点数)
• 是字符串
• b代表布尔值

所有您需要做的就是检查DB列具有哪些类型.您将看到数据库中的类型具有不同的名称(如varchar，float等).如果您用谷歌搜索这个名称，您会发现它们类似于字符串，整数，双精度和布尔型.因此，您必须根据列类型(字符串≆varchar，双精度浮点数，字符串≆tinytext，字符串≆日期/日期时间等)选择最佳匹配类型，并应确保值(您的$ _POST变量)实际上与您定义的类型匹配.

All you have to do is to check what types you DB columns have. You will see that the types in the database have different names (like varchar, float etc.). If you google for this names you will find out that they are similar to string, integer, double and boolean. So you have to choose the best matching type depending on the column type (string ≆ varchar, double ≆ float, string ≆ tinytext, string ≆ date/datetime etc. pp.) and you should ensure that the values (your $_POST variables) actually match the type you defined.

假设您所有的列都是类似varchar之类的文本，则第一个参数看起来像是'ssssssssssssssssssssss'(21 times s)或'ssssssssssssssssssss'(如果采用了unter_cordinator实例的列的类型为int(仅用于).

Assuming that all your columns are of an text like type like varchar, the first argument would look like 'sssssssssssssssssssss' (21 times s) or 'ssssssssssssssissssss' if the column that takes the volunteer_cordinator is of type int (just for instance).

完成此操作后，您应该仔细检查f (mysqli_connect_errno())是与复制粘贴相关的错误，还是实际上错过了代码中的i(应该为if (mysqli_connect_errno())).

After you have done this you should double check if f (mysqli_connect_errno()) is a copy&paste related mistake or if you have actually missed the i in your code (should be if (mysqli_connect_errno())).

如果您已经检查过应该考虑写$_POST['xyz']而不是'$_POST[xyz]'，它将对您有所帮助('标记字符串的开始/结尾， xyz 是实际上是这里的字符串).

If you have checked that you should consider to write $_POST['xyz'] instead of '$_POST[xyz]', it will help you, really (' marks the start/end of a string and xyz is in fact the string here).

如果仍然遇到错误，请通过在文件顶部添加error_reporting(E_ALL);启用更详细的错误信息(出于安全考虑，您应该在网站上线时将其删除)，然后提出一个新问题

If you still encounter errors enable more detailed error information by adding error_reporting(E_ALL); at the top of your file (you should remove this for security reasons when your site goes live) and ask a new question.

再次检查您的MySQL连接字符串(在mysql()方法中传递的参数).您确定密码以@开头并以句号结尾吗?顺便说一句，您不应该在公共场合发布密码等.

Double check your MySQL connection string (the arguments you pass in the mysql() method). Are you sure that your password starts with an @ and ends with a full stop? By the way, you shouldn't post passwords etc. in the public.

通过运行仅包含

<?php
// Show all information, defaults to INFO_ALL
phpinfo();
?>

并检查输出是否类似以下内容:

and check the output for something like this:

这篇关于我可以在SQL查询中使用htmlentities()吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！