用PDO和prepared语句替换mysql_ *函数 [英] Replacing mysql_* functions with PDO and prepared statements

问题描述

我一直在做 mysql_connect ， mysql_pconnect 的简单连接：

  $ db = mysql_pconnect（'* host *'，'* user *'，'* pass *'）; 
 
 if（！$ db）{
 echo（< strong>错误：< / strong>无法连接到数据库！ 
 exit; 
} 
 
 mysql_select_db（'* database *'）; 
 

在使用这个时，我总是使用简单的方法在查询之前转义任何数据， INSERT ， SELECT ， UPDATE 或 DELETE 使用 mysql_real_escape_string

  $ name = $ _POST ['name']; 
 
 $ name = mysql_real_escape_string（$ name）; 
 
 $ sql = mysql_query（SELECT * FROM`users` WHERE（`name` ='$ name'））或die（mysql_error 
  

现在我明白这是安全的，在一定程度上！

它逃避危险的字符;但是，它仍然容易受到其他可能包含安全字符的攻击，但可能对显示数据有害，或在某些情况下，恶意修改或删除数据。

我搜索了一下，发现了PDO，MySQLi和prepared语句。是的，我可能迟到了游戏，但我已经阅读了许多，许多教程（tizag，W3C，博客，谷歌搜索）在那里，没有一个人提到这些。看起来很奇怪为什么，因为只是逃避用户输入真的不安全，而不是好的做法说至少。是的，我知道你可以使用Regex来处理它，但仍然，我相信这还不够吗？

这是我的理解，使用PDO /准备语句是一种更安全的方法，用于在用户输入给出变量时存储和检索数据库中的数据。唯一的麻烦是，切换（特别是在非常困扰我的方式/习惯的编码以后）是有点困难。

现在我明白了连接到我的数据库使用PDO我会使用

  $ hostname ='* host *'; 
 $ username ='* user *'; 
 $ password ='* pass *'; 
 $ database ='* database *'
 
 $ dbh = new PDO（mysql：host = $ hostname; dbname = $ database，$ username，$ password）; 
 
 if（$ dbh）{
 echo'Connected to database'; 
} else {
 echo'无法连接数据库'; 
} 
  

现在，函数名称不同，所以我的 mysql_query ， mysql_fetch_array ， mysql_num_rows 等工作。所以我不得不读取/记住一些新的，但这是我困惑的地方。

如果我想插入数据从一个符号注册表单，我怎么会这样做，但主要是我如何安全地去呢？我假设这是准备语句进来的地方，但使用它们，这消除了使用 mysql_real_escape_string 的需要？我知道 mysql_real_escape_string 需要你通过 mysql_connect / 连接到数据库mysql_pconnect 所以现在我们不使用这个函数不会产生一个错误？

我已经看到了不同的方法来接近PDO方法，例如，我看到：variable 和？，我认为是所谓的占位符这是错误的）。

但我认为这大概是从数据库中获取用户应该做什么的想法

  $ user_id = $ _GET ['id']; //例如从URL查询字符串
 
 $ stmt = $ dbh-> prepare（SELECT * FROM`users`WHERE`id` =：user_id）; 
 
 $ stmt-> bindParam（'：user_id'，$ user_id，PDO :: PARAM_INT）; 
  

但是我被困在一些事情上，如果变量不是一个数字，一个文本字符串，如果我没有错误，你必须在 PDO：PARAM_STR 之后给出一个长度。但是如果你不确定从用户输入的数据给出的值，你怎么能给出一个设定的长度，它可以每次改变？无论如何，据我所知，显示的数据，你然后做

  $ stmt-> execute（）; 
 
 $ result = $ stmt-> fetchAll（）; 
 
 // 
 
 foreach（$ result as $ row）{
 echo $ row ['user_id']。'< br />'; 
 echo $ row ['user_name']。'< br />'; 
 echo $ row ['user_email']; 
} 
 
 //或
 
 foreach（$ result as $ row）{
 $ user_id = $ row ['user_id']; 
 $ user_name = $ row ['user_name']; 
 $ user_email = $ row ['user_email']; 
} 
 
 echo（。$ user_id。< br />\".$user_name.\"<br />\".$user_email。）; 
  

现在，这一切是否安全？

如果我是对的，插入数据是一样的，例如：

  $ username = $ _POST ['username']; 
 $ email = $ _POST ['email']; 
 
 $ stmt = $ dbh-> prepare（INSERT INTO`users`（username，email）
 VALUES（：username，：email））; 
 
 $ stmt-> bindParam（'：username，$ username，PDO :: PARAM_STR，？_LENGTH_？）; 
 $ stmt-> bindParam（'：email，$ email，PDO :: PARAM_STR，？_LENGTH_？）; 
 
 $ stmt-> execute（）; 
  

这样可行吗？如果是正确的，我将为？_ LENGTH _？输入什么值？

我回答我的问题，我已经完全错了吗？

$ b < ve到目前为止已经非常有帮助，不能感谢你们足够了！每个人都有+1打开我的眼睛，有点不同的东西。很难选择最好的答案，但我认为Shrapnel集合应该得到它，因为一切都被覆盖，甚至进入其他数组的自定义库，我不知道！

但感谢大家：）

解决方案

感谢有趣的问题。这里：

它转义危险字符，

$ b b

您的概念完全是错误。

事实上，危险角色是一个神话，没有。
和mysql_real_escape_string转义，但只是一个字符串分隔符。从这个定义可以得出结论，它的局限性 - 它只适用于字符串。

其他可能包含安全字符但可能对显示数据有害的攻击，或在某些情况下，恶意修改或删除数据。

说到数据库，

• 对于字符串不容易受到攻击。只要您的字符串被引用和转义，他们不能恶意修改或删除数据。 *


• 对于其他数据typedata - 是的，它是无用的。但不是因为它有点不安全，而只是因为不当使用。

对于显示数据，我认为在PDO相关问题中为 offtopic

转义用户输入

< blockquote>

^^^另一个妄想注意！

• 用户输入与转义无关。。正如你可以从前面的定义学习，你必须转义字符串，而不是任何用户输入。所以，再次：

  
  
  
  
  
  • 您有转义字符串，无论其来源
  
  
  
  

点？

现在，我希望你了解逃脱的限制以及危险字符的误解。

根据我的理解，使用PDO /预备语句是一个更安全的

不是真的。

事实上，我们可以动态添加四个不同的查询部分：

• 一个字符串


• 一个数字


• 一个标识符


• 一个语法关键字。

因此，您可以看到转义只涵盖一个问题。 （但是，如果您将数字视为字符串（将其置于引号中），，则可以使它们也安全）

准备语句覆盖 - ugh - 整个2 isues！一个大问题 - ）

对于其他2个问题，请参阅我早先的答案，在PHP中向数据库提交字符串时，我应该使用htmlspecialchars来处理非法字符（）或使用正则表达式？

现在，函数名称不同，所以我的mysql_query，mysql_fetch_array，mysql_num_rows等工作。

这是另一个严重的PHP 用户 ，一个灾难：

即使使用旧的mysql驱动程序，也不要在代码中使用裸API函数！一个人必须把它们在一些库函数为日常使用！ （不是作为一些魔法礼，但只是为了使代码更短，更少重复，防错，更一致和可读性）。

再次提出您的问题。

但是通过使用它们，这消除了使用像mysql_real_escape_string的需要？

是。

但我认为这大概是从数据库中获取用户应该做的事情。

不要抓取，而是向查询中添加任何数据

！

你必须在PDO后给出一个长度：PARAM_STR如果我没有错误

你可以， 。

现在，这是否安全？

在数据库安全方面，这段代码中没有弱点。没有什么可以在这里安全。

显示安全性 - 只需在此网站搜索 XSS 关键字。

希望我谈谈这个问题。

BTW，对于长插入，你可以使用我写的一些功能， a href =http://stackoverflow.com/questions/3773406/insert-update-helper-function-using-pdo/3921798#3921798>使用PDO插入/更新助手函数

但是，我现在不使用预先准备的语句，因为我喜欢使用我上面提到的图书馆 我自制的占位符。所以，为了对抗由下面的riha发布的代码，它将会短到这两行：

  $ sql = SELECT * FROM` users` WHERE`name` =？s AND`type` =？s AND`active` =？i'; 
 $ data = $ db-> getRow（$ sql，$ _ GET ['name']，'admin'，1）; 
  

但是当然你也可以使用预编译语句来使用相同的代码。

---

*（是的，我知道Schiflett的恐怖故事）

I've always done the simple connection of mysql_connect, mysql_pconnect:

$db = mysql_pconnect('*host*', '*user*', '*pass*');

if (!$db) {
    echo("<strong>Error:</strong> Could not connect to the database!");
    exit;
}

mysql_select_db('*database*');

While using this I've always used the simple method to escape any data before making a query, whether that be INSERT, SELECT, UPDATE or DELETE by using mysql_real_escape_string

$name = $_POST['name'];

$name = mysql_real_escape_string($name);

$sql = mysql_query("SELECT * FROM `users` WHERE (`name` = '$name')") or die(mysql_error());

Now I understand this is safe, to an extent!

It escapes dangerous characters; however, it is still vulnerable to other attacks which can contain safe characters but may be harmful to either displaying data or in some cases, modifying or deleting data maliciously.

So, I searched a little bit and found out about PDO, MySQLi and prepared statements. Yes, I may be late to the game but I've read many, many tutorials (tizag, W3C, blogs, Google searches) out there and not a single one has mentioned these. It seems very strange as to why, as just escaping user input really isn't secure and not good practice to say the least. Yes, I'm aware you could use Regex to tackle it, but still, I'm pretty sure that's not enough?

It is to my understanding that using PDO/prepared statements is a much safer way to store and retrieve data from a database when the variables are given by user input. The only trouble is, the switch over (especially after being very stuck in my ways/habits of previous coding) is a little difficult.

Right now I understand that to connect to my database using PDO I would use

$hostname = '*host*';
$username = '*user*';
$password = '*pass*';
$database = '*database*'

$dbh = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);

if ($dbh) {
    echo 'Connected to database';
} else {
    echo 'Could not connect to database';
}

Now, function names are different so no longer will my mysql_query, mysql_fetch_array, mysql_num_rows etc work. So I'm having to read/remember a load of new ones, but this is where I'm getting confused.

If I wanted to insert data from say a sign up/registration form, how would I go about doing this, but mainly how would I go about it securely? I assume this is where prepared statements come in, but by using them does this eliminate the need to use something like mysql_real_escape_string? I know that mysql_real_escape_string requires you to be connected to a database via mysql_connect/mysql_pconnect so now we aren't using either won't this function just produce an error?

I've seen different ways to approach the PDO method too, for example, I've seen :variable and ? as what I think are known as place holders (sorry if that is wrong).

But I think this is roughly the idea of what should be done to fetch a user from a database

$user_id = $_GET['id']; // For example from a URL query string

$stmt = $dbh->prepare("SELECT * FROM `users` WHERE `id` = :user_id");

$stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);

But then I'm stuck on a couple things, if the variable wasn't a number and was a string of text, you have to given a length after PDO:PARAM_STR if I'm not mistaken. But how can you give a set length if you're not sure on the value given from user in-putted data, it can vary each time? Either way, as far as I know to display the data you then do

$stmt->execute();

$result = $stmt->fetchAll();

// Either

foreach($result as $row) {
    echo $row['user_id'].'<br />';
    echo $row['user_name'].'<br />';
    echo $row['user_email'];
}

// Or

foreach($result as $row) {
    $user_id = $row['user_id'];
    $user_name = $row['user_name'];
    $user_email = $row['user_email'];
}

echo("".$user_id."<br />".$user_name."<br />".$user_email."");

Now, is this all safe?

If I am right, would inserting data be the same for example:

 $username = $_POST['username'];
 $email = $_POST['email'];

 $stmt = $dbh->prepare("INSERT INTO `users` (username, email)
                        VALUES (:username, :email)");

 $stmt->bindParam(':username, $username, PDO::PARAM_STR, ?_LENGTH_?);
 $stmt->bindParam(':email, $email, PDO::PARAM_STR, ?_LENGTH_?);

$stmt->execute();

Would that work, and is that safe too? If it is right what value would I put in for the ?_LENGTH_?? Have I got this all completely wrong?

UPDATE

The replies I've had so far have been extremely helpful, can't thank you guys enough! Everyone has got a +1 for opening my eyes up to something a little different. It's difficult to choose the top answer, but I think Col. Shrapnel deserves it as everything is pretty much covered, even going into other arrays with custom libraries which I wasn't aware of!

But thanks to all of you:)

解决方案

Thanks for the interesting question. Here you go:

It escapes dangerous characters,

Your concept is utterly wrong.
In fact "dangerous characters" is a myth, there are none. And mysql_real_escape_string escaping but merely a string delimiters. From this definition you can conclude it's limitations - it works only for strings.

however, it is still vulnerable to other attacks which can contain safe characters but may be harmful to either displaying data or in some cases, modifying or deleting data maliciously.

You're mixing here everything.
Speaking of database,

• for the strings it is NOT vulnerable. As long as your strings being quoted and escaped, they cannot "modify or delete data maliciously".*
• for the other data typedata - yes, it's useless. But not because it is somewhat "unsafe" but just because of improper use.

As for the displaying data, I suppose it is offtopic in the PDO related question, as PDO has nothing to do with displaying data either.

escaping user input

^^^ Another delusion to be noted!

• a user input has absolutely nothing to do with escaping. As you can learn from the former definition, you have to escape strings, not whatever "user input". So, again:

  • you have escape strings, no matter of their source
  • it is useless to escape other types of data, no matter of the source.

Got the point?
Now, I hope you understand the limitations of escaping as well as the "dangerous characters" misconception.

It is to my understanding that using PDO/prepared statements is a much safer

Not really.
In fact, there are four different query parts which we can add to it dynamically:

• a string
• a number
• an identifier
• a syntax keyword.

so, you can see that escaping covers only one issue. (but of course, if you treat numbers as strings (putting them in quotes), when applicable, you can make them safe as well)

while prepared statements cover - ugh - whole 2 isues! A big deal ;-)

For the other 2 issues see my earlier answer, In PHP when submitting strings to the database should I take care of illegal characters using htmlspecialchars() or use a regular expression?

Now, function names are different so no longer will my mysql_query, mysql_fetch_array, mysql_num_rows etc work.

That is another, grave delusion of PHP users, a natural disaster, a catastrophe:

Even when utilizing old mysql driver, one should never use bare API functions in their code! One have to put them in some library function for the everyday usage! (Not as a some magic rite but just to make the code shorter, less repetitive, error-proof, more consistent and readable).

The same goes for the PDO as well!

Now on with your question again.

but by using them does this eliminate the need to use something like mysql_real_escape_string?

YES.

But I think this is roughly the idea of what should be done to fetch a user from a database

Not to fetch, but to add a whatever data to the query!

you have to given a length after PDO:PARAM_STR if I'm not mistaken

You can, but you don't have to.

Now, is this all safe?

In terms of database safety there are just no weak spots in this code. Nothing to secure here.

for the displaying security - just search this site for the XSS keyword.

Hope I shed some light on the matter.

BTW, for the long inserts you can make some use of the function I wrote someday, Insert/update helper function using PDO

However, I am not using prepared statements at the moment, as I prefer my home-brewed placeholders over them, utilizing a library I mentioned above. So, to counter the code posted by the riha below, it would be as short as these 2 lines:

$sql  = 'SELECT * FROM `users` WHERE `name`=?s AND `type`=?s AND `active`=?i';
$data = $db->getRow($sql,$_GET['name'],'admin',1);

But of course you can have the same code using prepared statements as well.

---

* (yes I am aware of the Schiflett's scaring tales)

这篇关于用PDO和prepared语句替换mysql_ *函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！