当前位置:主页 > PHP开发 > pdo >

PDO执行增、删、改和查SQL语句详解

栏目:pdo 来源: 微信小程序开发网 阅读: 2016-12-12
  在教程“什么是数据库抽象层pdo”已经介绍了pdo的很多好处,其中使用pdo不仅编写代码方便,而且,比面向过程的执行sql语句更加安全,在那个教程中只把pdo编写代码简洁优点体现出来了,而没有把pdo的安全性能体现出来,本教程既体现出pdo的代码简洁又展示了pdo的安全优点。

面向过程的操作数据缺点:

  当操作数据库表时,面向过程操作数据库表时如果不注意的话就会让别人通过sql注入进行破坏或入侵,例如,下面是保存在phpos.php里面的一段删除数据库表中的记录的sql语句:
  //连接数据库略去
  $id = $_GET['id'];
  $sql = "DELETE FROM `dede_archives` where id ='$id'";
  mysql_query($sql);

  这三行代码就可以实现删除数据库表dede_archives里面的某一条记录,看上去这段代码很正常,其实,这段代码很危险很可能让别人使用sql注入一次删除您表中的所有数据。

  假设有人通过浏览器地址栏输入如下代码:
http://www.phpos.net/index.php?id=5' or 1='1


  这样把id=5' or 1='1传到上面的sql语句里面后就变成了:
$sql = "DELETE FROM `dede_archives` where id ='5' or 1='1' ";


  这样1='1'永远为真,上面的sql语句相当于执行了清空数据库表中的所有数据,这就是一个很明显的sql注入,当然,这里只是举了一个其中一个常见的sql注入,其它的还有很多种。

  我们使用面向过程的方法可以对上面的注入进行处理一样可以避免,比如可以使用转义把特殊字符进行转义,但是,处理起来相对比较麻烦,下面让我们看一下pdo是如何解决类似的注入问题的。


pdo简介

  pdo里面包括三类和一个驱动分别是:pdo类、PDOException类、PDOStatement类和pdo驱动。

  pdo类:只有在这个类里面才有构造函数,其它的类中并没有构造函数。

  PDOException类:错误处理类,主要是解决在使用pdo时可能出现的错误。

  PDOStatement类:处理sql语句时用到的类,如果使用pdo里面的query查询后,则返回的结果是一个PDOStatement对象,可以直接使用这个对象里面的方法。

  大体了解上面这些基本的pdo内容就可以了,下面我们将使用pdo来具体的看一下pdo是如何操数据库表的。


使用pdo删除数据库表中的某条记录

  1)连接数据库并设置错误的异常模式:

try {

    $pdo = new PDO("mysql:host=localhost;dbname=dedecmsv57utf8sp1", "root", "123456");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch (PDOException $e) {

    echo "数据库连接失败".$e->getMessage();
    exit;
    
}


  2)删除数据库表dede_archives的id=2的记录:

  在上面的文件phpos.php里面,在上面的代码后面加上如下删除数据库表记录的代码:
try {
    
    $pdostatement = $pdo->prepare("DELETE FROM `dede_archives` WHERE id=? ");
    $pdostatement->execute(array($_GET['id']));

} catch (PDOException $e) {

    echo '错误:'.$e->getMessage();
    
}


实验测试:

  没有删除前这个id记录如下图所示:

id=2

  在浏览器地址栏里面输入:http://localhost/phpos.php?id=2

  再来查看数据库表中id为2的记录,结果已经删除了,如下图所示:
已经删除id

  再来看看注入问题是否已经解决:  http://www.phpos.net/phpos.php?id=3' or 1='1

  当我在浏览器上面执行回车后,查看数据库表并没有什么变化,这就说明我们已经很容易的解决了sql注入问题。

  从上面的代码可以看出我们并没有做什么转义什么的,而只是在编写代码的时候比面向过程的稍微有一点区别。这说明防注入pdo已经帮我们做好了,我们只需要使用pdo提供的类里面的函数即可,只要按照pdo的编写要求就可能防止sql注入问题。


pdo防注入原理

  为什么上面的代码可以实现防sql注入呢?要弄明白这个问题,就要弄明白pdo处理sql语句的机制。

  pdo处理sql语句是这样的:

  对于面向过程的sql语句:当把程序提交到数据库服务器后,服务器进行编译成二进制并执行之,这样面向过程的sql语句提交过来的就是已经写好的完整的sql语句,所以,可以很容易的注入(如果没有写防注入语句的话)。

  对于使用pod的sql语句:当把程序提交到数据库服务器后,pdo会编译提交过来的sql语句成二进制代码,但是,此时pdo不会去执行而是放在数据库服务器里面等通过函数execute()传递过来值后,再去执行已经编译好的sql语句,而传递过来的值也会被编译成一个值,例如,上面的注入id=3' or 1='1当执行execute()函数时,就会把这里面的参数id=3' or 1='1编译成一个值,也就是把3' or 1='1 编译成一个值,这样入侵者无论怎么写都没有用,因为,不管怎么写这个条件,pdo都把他处理成一个值,这相当在执行sql语句前作了预先的处理,而面向过程的则不处理id=3' or 1='1,而是直接执行sql语句,这是二者的根本的区别,这也是为什么使用pdo后就可以预防sql注入。

pdo的增、删、改和查操作编写方法

  1)在函数prepare()里面用问号“?”代替要处理的sql语句里面的参数值,像上面的删除记录条件里面的id的值。

  2)用函数bindPram()绑定参数(也就是绑定问号“?”):在sql语句里面用问号代替的内容,需要使用使用函数bindPram()进行绑定,也就是问号到底是如何知道哪个对应哪个,上例子中只有一个问号,如果有多个问号如何区别呢?就是通过绑定函数来对应的绑定问号,这样程序就知道哪个值对应哪个问号。

  3)执行函数execute():如果绑定参数后直接执行这个函数,在这个函数里面不需要使用参数,但是,如果没有绑定则需要传一个“数组”作为参数,例如,上面举的删除的实例。


举例

  例如,向表dede_archives执行插入几条数据。

  1)编写sql语句:
$pdostatement = $pdo->prepare("INSERT INTO `dede_archives`(id,typeid,channel,title,writer)VALUES(?,?,?,?,?) ");

    请注意这里面四个问号对应表中的typeid,channel,title和writer,且问号不用加单引号或双引号或者其它符号,只写上问号代替这些字段的值。

  2)绑定参数:
       $pdostatement->bindParam(1,$id);
    $pdostatement->bindParam(2,$typeid);
    $pdostatement->bindParam(3,$channel);
    $pdostatement->bindParam(4,$title);
    $pdostatement->bindParam(5,$writer);

  请注意上面的参数例如$typeid是随便起的,也就是这说这里的变量可以自定义,但是,为了方法与上面要插入的数据的字段相对应,我这里面直接用字段名作为变量名。

  还有一点要注意上面的顺序不可以改变,例如,上面变成:
        $pdostatement->bindParam(1,$id);
    $pdostatement->bindParam(3,$typeid);
    $pdostatement->bindParam(2,$channel);
    $pdostatement->bindParam(4,$title);
    $pdostatement->bindParam(5,$writer);

  这样导致的结果就是字段channel和title的值互换了,所以,假设channel本来保存的是频道id,而title保存的是标题,如果顺序不对就变成了title保存了频道id,而字段channel则保存了标题内容,这样就会出错,所以,顺序一定与插入的字段一一对应。

  赋值给变量:上面只是把问号与变量一一绑定,现在就要对这四个变量进行赋值。
        $id = 100;
      $typeid = 8;
    $channel = 1;
    $title = "l love you!";
    $writer ="phpos.net";


  3)执行sql语句:$pdostatement->execute();

  如果是绑定的参数,函数execute()就不需要写参数了。

  执行上面的代码,然后,到数据库里面看看是不是已经把数据插入到了数据库表dede_archives里面了,如下图所示:


  已经成功插入到了数据库表里面了。


插入多条记录:

  如果想插入多条记录,例如,一次插入二条只需要在绑定的函数后面给变量二次赋值,完全代码如下:

<?php

try {

    $pdo = new PDO("mysql:host=localhost;dbname=dedecmsv57utf8sp1", "root", "123456");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch (PDOException $e) {

    echo "数据库连接失败".$e->getMessage();
    exit;
    
}

try {
    
    $pdostatement = $pdo->prepare("INSERT INTO `dede_archives`(id,typeid,channel,title,writer)VALUES(?,?,?,?,?) ");

    $pdostatement->bindParam(1,$id);
    $pdostatement->bindParam(2,$typeid);
    $pdostatement->bindParam(3,$channel);
    $pdostatement->bindParam(4,$title);
    $pdostatement->bindParam(5,$writer);

    $id = 1001;
    $typeid = 9;
    $channel = 2;
    $title = "love you!";
    $writer ="phpos.net";
    $pdostatement->execute();

    $id = 1002;
    $typeid = 0;
    $channel = 2;
    $title = "love me!";
    $writer ="www.phpos.net";
    $pdostatement->execute();

} catch (PDOException $e) {

    echo '错误:'.$e->getMessage();
    
}


  查看数据库表如下图所示:


 
多了二条数据说明插入成功。


pdo中sql语句中的参数分类

  sql语句中的参数分为问号参数和名字参数:在sql语句中使用问号代替参数称为问号参数;在sql语句中使用名字来代替参数称为名字参数。

  上面实例是使用的问号参数,问号参数在绑定参数时顺序必须正确,否则,会把保存或更新等内容给弄错。而名字参数则不用考虑顺序问题,这样就可以克服万一弄错了顺序而导致保存或更新等的内容。


名字参数

  名字的使用格式:冒号+名字,其中名字是自定义的。

  上面的sql语句就变成了:
$pdostatement = $pdo->prepare("INSERT INTO `dede_archives`(id,typeid,channel,title,writer)VALUES(:id, :typeid, :channel, :title, :writer)");

  绑定的方式有两种:

  一种是使用冒号如下所示:
       $pdostatement->bindParam(":id",$id);
    $pdostatement->bindParam(":typeid",$typeid);
    $pdostatement->bindParam(":channel",$channel);
    $pdostatement->bindParam(":title",$title);
    $pdostatement->bindParam(":writer",$writer);

  另一种是不使用冒号:
        $pdostatement->bindParam("id",$id);
    $pdostatement->bindParam("typeid",$typeid);
    $pdostatement->bindParam("channel",$channel);
    $pdostatement->bindParam("title",$title);
    $pdostatement->bindParam("writer",$writer);


  这两种方式都可以,可以根据自己的喜好来编写代码,其它跟上面的举的例子完全一样,这就是名字参数。

  名字参数和问号参数是键值对的形式方式绑定,问号参数相当于数组中的“索引”数组,而名字参数在绑定时相当于“关联”数组。

  绑定函数有两个必须的参数,也可以使用第三个参数用来指定变量的类型:$pdostatement->bindParam(":title",$title,PDO::PARAM_STR)表示变量$title是字符串类型,但是,因为php是弱类型的语言,默认会根据变量的值进行判断其类型,所以,一般情况下这个绑定函数的第三个参数不使用。

使用函数execute()简化代码

  上面代码是不是感觉有点麻烦,如果插入100条数据上面的代码要写很多,我们可以使用函数execute()来简化上面插入数据库表的操作。

  函数execute()即可以像上面那样执行sql语句,而且还可以在绑定参数并执行sql语句,而上面我们已经讲过了,绑定的参数其实要么是索引数组,要么是关联数组,所以,在向函数execute()传递参数时传递一个关联或索引数组即可。

问号参数方式:
$pdostatement = $pdo->prepare("INSERT INTO `dede_archives`(id,typeid,channel,title,writer)VALUES(?,?,?,?,?) ");

    $pdostatement->execute(array('444','2','4','love2','phpos.net'));
    $pdostatement->execute(array('445','2','5','love3','phpos.net'));
    $pdostatement->execute(array('446','2','6','love4','phpos.net'));


名字参数方式:
$pdostatement = $pdo->prepare("INSERT INTO `dede_archives`(id,typeid,channel,title,writer)VALUES(:id, :typeid, :channel, :title, :writer)");
    $pdostatement->execute(array('id'=>'555','typeid'=>'2','channel'=>'4','title'=>'love2','writer'=>'phpos.net'));
    $pdostatement->execute(array(':id'=>'666',':typeid'=>'2',':channel'=>'4',':title'=>'love2',':writer'=>'phpos.net'));
    $pdostatement->execute(array(':id'=>'777',':typeid'=>'2',':channel'=>'4',':title'=>'love2',':writer'=>'phpos.net'));

  请注意:上面名字参数方式中参数里面的我使用了冒号加键作为数组的键,另一个直接使用名字作为键,这两种方法都可以。

  上面两种方式分别向数据库表中插入了三条记录,这种方式就比上面使用绑定函数bindParam()要简单的多,但是,不是最简单的。


最简洁方式:

  因为插入到表中的数据要么从地址栏获取,要么从表单获取,所以,上面两种方式还可以进行简化如下所示:

$pdostatement = $pdo->prepare("INSERT INTO `dede_archives`(id,typeid,channel,title,writer)VALUES(:id, :typeid, :channel, :title, :writer)");
$pdostatement->execute($_GET);

$pdostatement->execute($_POST);


  也就是上面的函数execute()里面的数组使用$_GET或$_POST来代替,这样就非常简洁了。

  这也是我们最喜欢使用pdo的重要原因,既简单又安全。

总结:上面只是通过一个插入数据库表和一个删除操作来说明pdo的优点和使用方法,对于其它sql语句像修改和查询等跟上面使用方法完全一样,唯一不同是sql语句不同,其它完全一样,所以,不用再举例子了,可以自己练习一下看看效果,检查一下是不是已经真正掌握了pdo的增、删、改和查等sql操作。