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
|
<?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 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_once($key, $this->connection->quote($value), $sql);
//preg_replace('/'.$key.'/', $this->connection->quote($value), $sql, 1); // outputs '123def abcdef abcdef'
}
}
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;
}
// return error code and binded SQL for checking error
public function getError(array $param=array()){
if( preg_match('/^[0]*$/' ,$this->errorCode()) ){
//var_dump($this->errorInfo());
return false;
}
$error = $this->errorInfo();
$error['sql'] = $this->getSQL($param);
return $error;
}
//do something audit before SQL is being executed
public function execute($bound_input_params = NULL){
/* $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);
}
}
//$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;
}
function pdo_showerror($sth, $q, $param=''){
//global $sth;
if(!$q && DEBUG)
{
print"Execute query error, because: <br/><pre>";
print $sth->getSQL().HTML_EOL;
var_dump( $sth->errorInfo() );
print"</pre>";
$dump = error_get_last();
adderrorlog( 'SQL error', $dump['message'].PHP_EOL.$sth->getSQL( (array)$param) );
exit;
}
}
function str_replace_once($str_pattern, $str_replacement, $string){
if (strpos($string, $str_pattern) !== false){
$occurrence = strpos($string, $str_pattern);
return substr_replace($string, $str_replacement, strpos($string, $str_pattern), strlen($str_pattern));
}
return $string;
}
|