smart-sql-queries
Code (php)
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
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
<?php
/**
* getSmartCommand
*
* <p>Returns a set of command names by detecting it's driver type</p>
* @param resource $dbh A sql connection resource
* @return array A set of command names
*
*/
function getSmartCommand($driver) {
// GLOBAL SmartCommandsDriver is used only to detect the connection once every script.
// can be overidden by setting $GLOBALS['SmartCommandsDriver'] to the driver of your choice.
// extend for your database here!
$commands = array(
'mysql' => array(
'query' => 'mysql_query',
'lastid' => 'mysql_insert_id',
'escape' => 'mysql_real_escape_string',
'error' => 'mysql_error',
'affected_rows' => 'mysql_affected_rows',
'fetchassoc' => 'mysql_fetch_assoc'
),
'pgsql' => array(
'query' => 'pg_query',
'lastid' => 'pg_last_oid',
'escape' => 'pg_escape_string',
'error' => 'pg_last_error',
'affected_rows' => 'pg_affected_rows',
'fetchassoc' => 'pg_fetch_assoc'
)
);
if (!isset($GLOBALS["SmartCommandsDriver"]) || !key_exists($commands[$GLOBALS["SmartCommandsDriver"]])) {
if (@pg_ping($driver)) {
$GLOBALS["SmartCommandsDriver"] = "pgsql";
} elseif (@mysql_ping($driver)) {
$GLOBALS["SmartCommandsDriver"] = "mysql";
} else {
trigger_error("No valid database connection");
}
}
return ($commands[$GLOBALS["SmartCommandsDriver"]]);
};
/**
* smartQuery
*
* <p>Returns results for a specific sql query. Select queries will return , replaces ? with a quoted/escaped corresponding array value. </p>
* @param string $query A query like "SELECT id FROM table WHERE field = ? AND nextfield = ?"
* @param array @params [a,b] Parameters corresponding with the number of questionmarks
* @return string SELECT id FROM table WHERE field = 'a' AND nextfield = 'b'
*
*/
function smartQuery() {
$arguments = func_get_args();
if (is_resource($arguments[0])) {
$dbh = array_shift($arguments);
} elseif (isset($GLOBALS['dbh']) && is_resource($GLOBALS['dbh'])) {
$dbh = $GLOBALS['dbh'];
} else {
trigger_error("No valid database connection, please provide a database resource as a first argument or define the global dbh");
return false;
}
$commands = getSmartCommand($dbh);
$query = array_shift($arguments);
// the second argument can either be an array or (multiple) values
if (isset($arguments) && isset($arguments[0]) && is_array($arguments[0])) {
$arguments = $arguments[0];
}
$query = escapeQuery($dbh, $query, $arguments);
if (isset($arguments)) {
$sth = $commands['query']($query);
} else {
return false;
}
if (!$sth) {
trigger_error("Error in smartQuery:" . $commands['error']() . "<br /><pre>" . print_r(debug_backtrace(),1) . "</pre>", E_USER_ERROR);
}
//remove the first whitespace
$query = preg_replace('/^(\s+)/', "", $query);
if (preg_match("/^select/i", $query)) {
$info = array();
while ($row = $commands['fetchassoc']($sth)) {
array_push($info,$row);
}
return $info;
} elseif (preg_match("/^insert/i", $query)) {
return $commands['lastid']($sth);
} elseif (preg_match("/^(update|delete)/i", $query)) {
// aantal updates terug geven
return $commands['affected_rows']($sth);
}
}
/**
* PDO/DBI-Like query escaping
*
* <p>Returns an escaped sql string, replaces ? with a quoted/escaped corresponding array value</p>
* @param string $query A query like "SELECT id FROM table WHERE field = ? AND nextfield = ?"
* @param mixed @params e.g. array('a','b') Parameters corresponding with the number of questionmarks
* @return string e.g. "SELECT id FROM table WHERE field = 'a' AND nextfield = 'b'"
*
*/
function escapeQuery () {
$arguments = func_get_args();
if (is_resource($arguments[0])) {
$dbh = array_shift($arguments);
} elseif (isset($GLOBALS['dbh']) && is_resource($GLOBALS['dbh'])) {
$dbh = $GLOBALS['dbh'];
} else {
trigger_error("No valid database connection, please provide a database resource as a first argument or define the global dbh");
return false;
}
$query = array_shift($arguments);
if (isset($arguments) && isset($arguments[0]) && is_array($arguments[0])) {
$arguments = $arguments[0];
}
foreach ($arguments as &$arg) {
$query = preg_replace("/\?/", escapeByType($arg), $query, 1);
}
return $query;
}
/**
* Specific escaping per type
*
* <p>Returns a quoted string, dependent on it's type, currently only recognizes integers</p>
*
*/
function escapeByType ($argument) {
$commands = getSmartCommand($dbh);
if (gettype($argument) == "integer" && is_numeric($argument)) {
// don't quote integers!
return $commands['escape']($argument);
} elseif (isnull($argument)) {
return "NULL";
} else {
return "'" . $commands['escape']($argument) . "'";
}
}
?>
/**
* getSmartCommand
*
* <p>Returns a set of command names by detecting it's driver type</p>
* @param resource $dbh A sql connection resource
* @return array A set of command names
*
*/
function getSmartCommand($driver) {
// GLOBAL SmartCommandsDriver is used only to detect the connection once every script.
// can be overidden by setting $GLOBALS['SmartCommandsDriver'] to the driver of your choice.
// extend for your database here!
$commands = array(
'mysql' => array(
'query' => 'mysql_query',
'lastid' => 'mysql_insert_id',
'escape' => 'mysql_real_escape_string',
'error' => 'mysql_error',
'affected_rows' => 'mysql_affected_rows',
'fetchassoc' => 'mysql_fetch_assoc'
),
'pgsql' => array(
'query' => 'pg_query',
'lastid' => 'pg_last_oid',
'escape' => 'pg_escape_string',
'error' => 'pg_last_error',
'affected_rows' => 'pg_affected_rows',
'fetchassoc' => 'pg_fetch_assoc'
)
);
if (!isset($GLOBALS["SmartCommandsDriver"]) || !key_exists($commands[$GLOBALS["SmartCommandsDriver"]])) {
if (@pg_ping($driver)) {
$GLOBALS["SmartCommandsDriver"] = "pgsql";
} elseif (@mysql_ping($driver)) {
$GLOBALS["SmartCommandsDriver"] = "mysql";
} else {
trigger_error("No valid database connection");
}
}
return ($commands[$GLOBALS["SmartCommandsDriver"]]);
};
/**
* smartQuery
*
* <p>Returns results for a specific sql query. Select queries will return , replaces ? with a quoted/escaped corresponding array value. </p>
* @param string $query A query like "SELECT id FROM table WHERE field = ? AND nextfield = ?"
* @param array @params [a,b] Parameters corresponding with the number of questionmarks
* @return string SELECT id FROM table WHERE field = 'a' AND nextfield = 'b'
*
*/
function smartQuery() {
$arguments = func_get_args();
if (is_resource($arguments[0])) {
$dbh = array_shift($arguments);
} elseif (isset($GLOBALS['dbh']) && is_resource($GLOBALS['dbh'])) {
$dbh = $GLOBALS['dbh'];
} else {
trigger_error("No valid database connection, please provide a database resource as a first argument or define the global dbh");
return false;
}
$commands = getSmartCommand($dbh);
$query = array_shift($arguments);
// the second argument can either be an array or (multiple) values
if (isset($arguments) && isset($arguments[0]) && is_array($arguments[0])) {
$arguments = $arguments[0];
}
$query = escapeQuery($dbh, $query, $arguments);
if (isset($arguments)) {
$sth = $commands['query']($query);
} else {
return false;
}
if (!$sth) {
trigger_error("Error in smartQuery:" . $commands['error']() . "<br /><pre>" . print_r(debug_backtrace(),1) . "</pre>", E_USER_ERROR);
}
//remove the first whitespace
$query = preg_replace('/^(\s+)/', "", $query);
if (preg_match("/^select/i", $query)) {
$info = array();
while ($row = $commands['fetchassoc']($sth)) {
array_push($info,$row);
}
return $info;
} elseif (preg_match("/^insert/i", $query)) {
return $commands['lastid']($sth);
} elseif (preg_match("/^(update|delete)/i", $query)) {
// aantal updates terug geven
return $commands['affected_rows']($sth);
}
}
/**
* PDO/DBI-Like query escaping
*
* <p>Returns an escaped sql string, replaces ? with a quoted/escaped corresponding array value</p>
* @param string $query A query like "SELECT id FROM table WHERE field = ? AND nextfield = ?"
* @param mixed @params e.g. array('a','b') Parameters corresponding with the number of questionmarks
* @return string e.g. "SELECT id FROM table WHERE field = 'a' AND nextfield = 'b'"
*
*/
function escapeQuery () {
$arguments = func_get_args();
if (is_resource($arguments[0])) {
$dbh = array_shift($arguments);
} elseif (isset($GLOBALS['dbh']) && is_resource($GLOBALS['dbh'])) {
$dbh = $GLOBALS['dbh'];
} else {
trigger_error("No valid database connection, please provide a database resource as a first argument or define the global dbh");
return false;
}
$query = array_shift($arguments);
if (isset($arguments) && isset($arguments[0]) && is_array($arguments[0])) {
$arguments = $arguments[0];
}
foreach ($arguments as &$arg) {
$query = preg_replace("/\?/", escapeByType($arg), $query, 1);
}
return $query;
}
/**
* Specific escaping per type
*
* <p>Returns a quoted string, dependent on it's type, currently only recognizes integers</p>
*
*/
function escapeByType ($argument) {
$commands = getSmartCommand($dbh);
if (gettype($argument) == "integer" && is_numeric($argument)) {
// don't quote integers!
return $commands['escape']($argument);
} elseif (isnull($argument)) {
return "NULL";
} else {
return "'" . $commands['escape']($argument) . "'";
}
}
?>