这两天公司使用的框架需求使用PDO方式操作数据库
于是就去查相关资料啊,然后发现了一个有意思的东西
PDO和在学校学的.net数据库操作方式差不多
.net
首先看下.net的方式,当然这是还在学校那会做联系的代码,刚整理的时候只有查询,稍微做了下修改,没跑过,估计改改也能用
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
//查询
SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["connectionstring"].ToString());
con.Open();
SqlCommand com = new SqlCommand();
com.Connection = con;
string Tsql = "select * from Student where ID=@stu_ID and PWD=@stu_PWD";
com.CommandText = Tsql;
com.Parameters.AddWithValue("stu_ID", "1");
com.Parameters.AddWithValue("stu_PWD", "2");
com.ExecuteReader();
con.Close();
//插入
SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["connectionstring"].ToString());
con.Open();
SqlCommand com = new SqlCommand();
com.Connection = con;
string Tsql = "insert into Student (ID=@stu_ID , PWD=@stu_PWD)";
com.CommandText = Tsql;
com.Parameters.AddWithValue("stu_ID", "1");
com.Parameters.AddWithValue("stu_PWD", "2");
com.ExecuteReader();
con.Close();
//修改
SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["connectionstring"].ToString());
con.Open();
SqlCommand com = new SqlCommand();
com.Connection = con;
string Tsql = "update Student set ID=@stu_ID , PWD=@stu_PWD";
com.CommandText = Tsql;
com.Parameters.AddWithValue("stu_ID", "1");
com.Parameters.AddWithValue("stu_PWD", "2");
com.ExecuteReader();
con.Close();
进入正题,PHP PDO
<?php
echo "问号方式第一种绑定方式<br>";
print_r(sele(1,1,db()));
echo "<br>问号方式第二种绑定方式<br>";
print_r(sele(1,2,db()));
echo "<br>问号方式第三种绑定方式<br>";
print_r(sele(1,3,db()));
echo "<br>别名方式第一种绑定方式<br>";
print_r(sele(2,1,db()));
echo "<br>别名方式第二种绑定方式<br>";
print_r(sele(2,2,db()));
echo "<br>别名方式第三种绑定方式<br>";
print_r(sele(2,3,db()));
//预处理语句,共有3种绑定方式
//查询
function sele($mode,$method,$db){
//?号的参数绑定
if($mode==1){
$sql="select * from app_ext_t where id=? and app_id=? and ext_key=?";
$res = $db->prepare($sql);
if($method=1){
$res->bindValue(1,'1');//对应第一个问号
$res->bindValue(3,'1');//对应第三个问号
$res->bindValue(2,278573612908);//对应第二个问号
}else if($method=1){
$res->bindParam(1,'1');//对应第一个问号
$res->bindParam(2,278573612908);//对应第三个问号
$res->bindParam(3,'1');//对应第二个问号
}
if($method=3){
$res->execute(array(1,278573612908,1));//$res->execute(array(id,app_id,ext_key))
}else{
$res->execute();
}
return $res->fetchall();
}else if($mode==2){
$sql="select * from app_ext_t where id=:id and app_id=:app_id and ext_key=:ext_key";
$res = $db->prepare($sql);
if($method=1){
$res->bindValue('id','1');
$res->bindValue('ext_key','1');
$res->bindValue('app_id',278573612908);
}else if($method=2){
$res->bindParam('id','1');
$res->bindParam('ext_key','1');
$res->bindParam('app_id',278573612908);
}
if($method=3){
$res->execute(array('id'=>1,'app_id'=>278573612908,'ext_key'=>1));//$res->execute(array(id,app_id,ext_key))
}else{
$res->execute();
}
return $res->fetchall();
}
}
echo "问号方式第一种绑定方式<br>";
print_r(inse(1,1,db()));
echo "<br>问号方式第二种绑定方式<br>";
print_r(inse(1,2,db()));
echo "<br>问号方式第三种绑定方式<br>";
print_r(inse(1,3,db()));
echo "<br>别名方式第一种绑定方式<br>";
print_r(inse(2,1,db()));
echo "<br>别名方式第二种绑定方式<br>";
print_r(inse(2,2,db()));
echo "<br>别名方式第三种绑定方式<br>";
print_r(inse(2,3,db()));
//插入
function inse($mode,$method,$db){
//?号的参数绑定
if($mode==1){
$sql="insert into app_ext_t (id,app_id,ext_key)value(?,?,?)";
$res = $db->prepare($sql);
if($method=1){
$res->bindValue(1,'1');//对应第一个问号
$res->bindValue(3,'1');//对应第三个问号
$res->bindValue(2,278573612908);//对应第二个问号
}else if($method=1){
$res->bindParam(1,'1');//对应第一个问号
$res->bindParam(2,278573612908);//对应第三个问号
$res->bindParam(3,'1');//对应第二个问号
}
if($method=3){
$res->execute(array(1,278573612908,1));//$res->execute(array(id,app_id,ext_key))
}else{
$res->execute();
}
return $res->rowCount();
}else if($mode==2){
$sql="insert into app_ext_t (id,app_id,ext_key)value(:id,:app_id,:ext_key)";
$res = $db->prepare($sql);
if($method=1){
$res->bindValue('id','1');
$res->bindValue('ext_key','1');
$res->bindValue('app_id',278573612908);
}else if($method=2){
$res->bindParam('id','1');
$res->bindParam('ext_key','1');
$res->bindParam('app_id',278573612908);
}
if($method=3){
$res->execute(array('id'=>1,'app_id'=>278573612908,'ext_key'=>1));//$res->execute(array(id,app_id,ext_key))
}else{
$res->execute();
}
return $res->rowCount();
}
}
echo "问号方式第一种绑定方式<br>";
print_r(update(1,1,db()));
echo "<br>问号方式第二种绑定方式<br>";
print_r(update(1,2,db()));
echo "<br>问号方式第三种绑定方式<br>";
print_r(update(1,3,db()));
echo "<br>别名方式第一种绑定方式<br>";
print_r(update(2,1,db()));
echo "<br>别名方式第二种绑定方式<br>";
print_r(update(2,2,db()));
echo "<br>别名方式第三种绑定方式<br>";
print_r(update(2,3,db()));
//修改
function update($mode,$method,$db){
//?号的参数绑定
if($mode==1){
$sql="update app_ext_t set id=?,app_id=?,ext_key=?";
$res = $db->prepare($sql);
if($method=1){
$res->bindValue(1,'1');//对应第一个问号
$res->bindValue(3,'1');//对应第三个问号
$res->bindValue(2,278573612908);//对应第二个问号
}else if($method=1){
$res->bindParam(1,'1');//对应第一个问号
$res->bindParam(2,278573612908);//对应第三个问号
$res->bindParam(3,'1');//对应第二个问号
}
if($method=3){
$res->execute(array(1,278573612908,1));//$res->execute(array(id,app_id,ext_key))
}else{
$res->execute();
}
return $res->rowCount();
}else if($mode==2){
$sql="update app_ext_t set id=:id,app_id=:app_id,ext_key=:ext_key";
$res = $db->prepare($sql);
if($method=1){
$res->bindValue('id','1');
$res->bindValue('ext_key','1');
$res->bindValue('app_id',278573612908);
}else if($method=2){
$res->bindParam('id','1');
$res->bindParam('ext_key','1');
$res->bindParam('app_id',278573612908);
}
if($method=3){
$res->execute(array('id'=>1,'app_id'=>278573612908,'ext_key'=>1));//$res->execute(array(id,app_id,ext_key))
}else{
$res->execute();
}
return $res->rowCount();
}
}
/**
* [db 连接数据库]
* @return [type] [object]
*/
function db(){
try{
$db = new PDO("mysql:host=localhost;dbname=entboos","root","root");
//$db = new PDO("mysql:host=localhost;dbname=entboos","root","root",array(PDO::ATTR_PERSISTENT => true));//长连接
return $db;
}catch(PDOException $e){
die("数据库连接失败".$e->getMessage());
}
}
声明:
本文采用
BY-NC-SA
协议进行授权,如无注明均为原创,转载请注明转自
一颗大萝北
本文地址: PHP PDO预处理(PDOStatement)方式与c#.net的SqlCommand差不多的方式实现增删改查,防止sql注入
本文地址: PHP PDO预处理(PDOStatement)方式与c#.net的SqlCommand差不多的方式实现增删改查,防止sql注入