1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
|
<?php
// http://daveyshafik.com/archives/605-debugging-pdo-prepared-statements.html for more info
class PDOTester extends PDO {
public function __construct($dsn, $username = null, $password = null, $driver_options = array())
{
parent::__construct($dsn, $username, $password, $driver_options);
$this->setAttribute(PDO::ATTR_STATEMENT_CLASS, array("PDOStatementTester", array($this)));
}
}
class PDOStatementTester extends PDOStatement {
const NO_MAX_LENGTH = -1;
protected $connection;
protected $bound_params = array();
protected $bound_input_params = array();
protected function __construct(PDO $connection)
{
$this->connection = $connection;
}
public function bindParam($paramno, &$param, $type = PDO::PARAM_STR, $maxlen = null, $driverdata = null)
{
$this->bound_params[$paramno] = array(
"value" => &$param,
"type" => $type,
"maxlen" => (is_null($maxlen)) ? self::NO_MAX_LENGTH : $maxlen,
// ignore driver data
);
$result = parent::bindParam($paramno, $param, $type, $maxlen, $driverdata);
}
public function bindValue($parameter, $value, $data_type = PDO::PARAM_STR)
{
$this->bound_params[$parameter] = array(
"value" => $value,
"type" => $data_type,
"maxlen" => self::NO_MAX_LENGTH
);
parent::bindValue($parameter, $value, $data_type);
}
public function getSQL($values = array())
{
$sql = $this->queryString;
if (sizeof($values) > 0) {
krsort($values);
foreach ($values as $key => $value) {
$sql = str_replace($key, $this->connection->quote($value), $sql);
}
}
if (sizeof($this->bound_input_params) > 0) {
foreach ($this->bound_input_params as $key => $value) {
$sql = str_replace($key, $this->connection->quote($value), $sql);
}
}
if (sizeof($this->bound_params)) {
foreach ($this->bound_params as $key => $param) {
$value = $param['value'];
if (!is_null($param['type'])) {
$value = self::cast($value, $param['type']);
}
if ($param['maxlen'] && $param['maxlen'] != self::NO_MAX_LENGTH) {
$value = self::truncate($value, $param['maxlen']);
}
if (!is_null($value)) {
$sql = str_replace($key, $this->connection->quote($value), $sql);
} else {
$sql = str_replace($key, "NULL", $sql);
}
}
}
return $sql;
}
public function execute($bound_input_params = NULL){
$this->bound_input_params = $bound_input_params;
if (sizeof($this->bound_input_params) > 0) {
krsort($this->bound_input_params);
}
$statment = explode(' ', $this->queryString, 5);
if(strtoupper($statment[0])=='DELETE'){
if( strpos($statment[2], 'AUDIT')===false ){
//echo "deleting".$statment[2];
//echo $this->queryString."<br/>";
//todo: write to auditlog
}else{
//echo "accessing audit table";
}
}
return parent::execute($bound_input_params);
}
static protected function cast($value, $type)
{
switch ($type) {
case PDO::PARAM_BOOL:
return (bool) $value;
break;
case PDO::PARAM_NULL:
return null;
break;
case PDO::PARAM_INT:
return (int) $value;
case PDO::PARAM_STR:
default:
return $value;
}
}
static protected function truncate($value, $length)
{
return substr($value, 0, $length);
}
}
function testPDO(){
/* $pdo = new PDOTester("sqlite::memory:");
$pdo->query("CREATE TABLE foo (bar TEXT, baz TEXT, num NUMERIC, empty TEXT)");
$query = $pdo->prepare("SELECT * FROM foo WHERE bar = :bar AND baz = :baz");
// Test with passed in array
echo $query->getSQL(array(":bar" => "foo", ":baz" => "bat")) . HTML_EOL;
$query = $pdo->prepare("SELECT * FROM foo WHERE bar = :bar AND baz = :baz AND num = :num AND empty=:empty");
// Test with bound params and values
$bar = "bar";
$baz = "baz";
$num = "0.1";
$empty = "empty!!";
// Bind Param
$query->bindParam(":bar", $bar);
// Bind Value
$query->bindValue(":baz", $baz);
// Bind With types
$query->bindParam(":num", $num, PDO::PARAM_INT);
$query->bindParam(":empty", $empty, PDO::PARAM_NULL);
echo $query->getSQL() . HTML_EOL;
// Change the vars
$bar = "foo";
$baz = "bat";
$num = "2.6";
$empty = "blah!";
echo $query->getSQL() . HTML_EOL;
// Bind with length
$query->bindParam(":bar", $bar, PDO::PARAM_STR, 2);
echo $query->getSQL() . HTML_EOL;*/
$sql = "update sys_language set languageDesc = :ldesc where ID = :ID";
$sth = $dbh->prepare($sql);
$sth->execute( array(':ldesc' => "Lang", ':ID' => 4) );
echo $sth->getSQL( array(':ldesc' => "Lang", ':ID' => 4) ) . HTML_EOL;
}
$dbh = new PDOTester($dsn, $user, $password);
//testPDO();
/**
* Replaces any parameter placeholders in a query with the value of that
* parameter. Useful for debugging. Assumes anonymous parameters from
* $params are are in the same order as specified in $query
*
* @param string $query The sql query with parameter placeholders
* @param array $params The array of substitution parameters
* @return string The interpolated query
*/
//public static
function showSQL($query, $params) {
$keys = array();
# build a regular expression for each parameter
foreach ($params as $key => $value) {
if (is_string($key)) {
$keys[] = '/:'.$key.'/';
} else {
$keys[] = '/[?]/';
}
}
$query = preg_replace($keys, $params, $query, 1, $count);
#trigger_error('replaced '.$count.' keys');
return $query;
}
?>
|