Changeset:
2a45140bb5e1
https://sourceforge.net/p/mrbs/hg-code/ci/2a45140bb5e156478c2ef721fc07977d036fc835
Author:
John Beranek <[email protected]>
Date:
Sat Sep 17 12:35:26 2016 +0100
Log message:
More SQL statements parameterised
diffstat:
web/auth/auth_db.inc | 16 ++--
web/auth/auth_db_ext.inc | 12 +-
web/dbsys.inc | 24 +++---
web/edit_area_room.php | 153 ++++++++++++++++++++++++++++++----------------
web/mrbs_sql.inc | 33 ++++++---
web/mysqli.inc | 37 ++++++----
web/pending.php | 7 +-
web/search.php | 22 +++--
web/upgrade/5/post.inc | 4 +-
web/upgrade/6/post.inc | 14 ++-
10 files changed, 196 insertions(+), 126 deletions(-)
diffs (truncated from 852 to 300 lines):
diff -r a07908a5aac3 -r 2a45140bb5e1 web/auth/auth_db.inc
--- a/web/auth/auth_db.inc Sat Sep 17 10:26:42 2016 +0100
+++ b/web/auth/auth_db.inc Sat Sep 17 12:35:26 2016 +0100
@@ -50,7 +50,7 @@
"
LIMIT 1";
- $res = sql_query($sql);
+ $res = sql_query($sql, array(utf8_strtolower($user)));
if ($res == FALSE)
{
trigger_error(sql_error(), E_USER_WARNING);
@@ -101,11 +101,12 @@
if ($do_rehash)
{
$sql = "UPDATE $tbl_users
- SET password_hash='".
- sql_escape(password_hash($pass, PASSWORD_DEFAULT)) ."'
+ SET password_hash=?
WHERE " .
sql_syntax_casesensitive_equals('name', utf8_strtolower($user));
- sql_command($sql);
+ sql_command($sql,
+ array(password_hash($pass, PASSWORD_DEFAULT),
+ utf8_strtolower($user)));
}
return $result;
@@ -129,7 +130,8 @@
return 0;
}
- $result = sql_query1("SELECT level FROM $tbl_users WHERE name='" .
sql_escape(utf8_strtolower($user)) . "' LIMIT 1");
+ $result = sql_query1("SELECT level FROM $tbl_users WHERE name=? LIMIT 1",
+ array(utf8_strtolower($user)));
return ($result == -1) ? 0 : $result;
}
@@ -148,10 +150,10 @@
$sql = "SELECT email
FROM $tbl_users
- WHERE name='" . sql_escape($user) . "'
+ WHERE name=?
LIMIT 1";
- $email = sql_query1($sql);
+ $email = sql_query1($sql, array($user));
return ($email == -1) ? '' : $email;
}
diff -r a07908a5aac3 -r 2a45140bb5e1 web/auth/auth_db_ext.inc
--- a/web/auth/auth_db_ext.inc Sat Sep 17 10:26:42 2016 +0100
+++ b/web/auth/auth_db_ext.inc Sat Sep 17 12:35:26 2016 +0100
@@ -62,13 +62,13 @@
$persist,
$port);
- // sql_syntax_casesensitive_equals() does the escaping. We need an exact
match -
+ // sql_syntax_casesensitive_equals() inserts a param placeholder so we must
pass the param. We need an exact match -
// MySQL allows trailing spaces when using an '=' comparison, eg 'john' =
'john '
$query = "SELECT " . sql_quote($auth['db_ext']['column_name_password']) .
" FROM " . sql_quote($auth['db_ext']['db_table']) .
" WHERE " .
sql_syntax_casesensitive_equals($auth['db_ext']['column_name_username'],
utf8_strtolower($user));
- $r = sql_query($query, $conn);
+ $r = sql_query($query, array(utf8_strtolower($user)), $conn);
if ($r === FALSE)
{
@@ -173,14 +173,14 @@
$persist,
$port);
- // sql_syntax_casesensitive_equals() does the escaping. We need an
exact match -
+ // sql_syntax_casesensitive_equals() inserts a param placeholder, so we
need to pass the param. We need an exact match -
// MySQL allows trailing spaces when using an '=' comparison, eg 'john' =
'john '
$query = "SELECT " . sql_quote($auth['db_ext']['column_name_level']) . "
FROM " . sql_quote($auth['db_ext']['db_table']) . "
WHERE " .
sql_syntax_casesensitive_equals($auth['db_ext']['column_name_username'],
utf8_strtolower($user)) . "
LIMIT 1";
- $r = sql_query($query, $conn);
+ $r = sql_query($query, array(utf8_strtolower($user), $conn);
if ($r === FALSE)
{
@@ -235,14 +235,14 @@
$persist,
$port);
- // sql_syntax_casesensitive_equals() does the escaping. We need an exact
match -
+ // sql_syntax_casesensitive_equals() inserts a param placeholder, so we need
to pass the param. We need an exact match -
// MySQL allows trailing spaces when using an '=' comparison, eg 'john' =
'john '
$query = "SELECT " . sql_quote($auth['db_ext']['column_name_email']) . "
FROM " . sql_quote($auth['db_ext']['db_table']) . "
WHERE " .
sql_syntax_casesensitive_equals($auth['db_ext']['column_name_username'],
utf8_strtolower($user)) . "
LIMIT 1";
- $r = sql_query($query, $conn);
+ $r = sql_query($query, array(utf8_strtolower($user)), $conn);
if ($r === FALSE)
{
diff -r a07908a5aac3 -r 2a45140bb5e1 web/dbsys.inc
--- a/web/dbsys.inc Sat Sep 17 10:26:42 2016 +0100
+++ b/web/dbsys.inc Sat Sep 17 12:35:26 2016 +0100
@@ -108,9 +108,9 @@
// Run an SQL query that doesn't produce results
-function sql_command($sql)
+function sql_command($sql, $params = array())
{
- if (func_num_args() > 1)
+ if (func_num_args() > 2)
{
$handle = func_get_arg(1);
$db_sys = $handle['system'];
@@ -125,15 +125,15 @@
}
$f = __NAMESPACE__ . "\\sql_${db_sys}_command";
- return $f($sql, $db_conn);
+ return $f($sql, $params, $db_conn);
}
// Run an SQL query that returns only one result - returns the result
// directly. SQL query must select only one column and one row.
-function sql_query1($sql)
+function sql_query1($sql, $params = array())
{
- if (func_num_args() > 1)
+ if (func_num_args() > 2)
{
$handle = func_get_arg(1);
$db_sys = $handle['system'];
@@ -148,16 +148,16 @@
}
$f = __NAMESPACE__ . "\\sql_${db_sys}_query1";
- return $f($sql, $db_conn);
+ return $f($sql, $params, $db_conn);
}
// Run an SQL query that returns a simple one dimensional array of results.
// The SQL query must select only one column. Returns an empty array if
// no results, or FALSE if there's an error
-function sql_query_array($sql)
+function sql_query_array($sql, $params = array())
{
- if (func_num_args() > 1)
+ if (func_num_args() > 2)
{
$handle = func_get_arg(1);
$db_conn = $handle['connection'];
@@ -167,7 +167,7 @@
$db_conn = NULL;
}
- $res = ($db_conn) ? sql_query($sql, $db_conn) : sql_query($sql);
+ $res = ($db_conn) ? sql_query($sql, $params, $db_conn) : sql_query($sql,
$params);
if ($res === FALSE)
{
@@ -186,9 +186,9 @@
// Run a SQL query, returns a result object or FALSE on failure
-function sql_query($sql)
+function sql_query($sql, $params = array())
{
- if (func_num_args() > 1)
+ if (func_num_args() > 2)
{
$handle = func_get_arg(1);
$db_sys = $handle['system'];
@@ -202,7 +202,7 @@
$db_conn = null;
}
$f = __NAMESPACE__ . "\\sql_${db_sys}_query";
- return $f($sql, $db_conn);
+ return $f($sql, $params, $db_conn);
}
diff -r a07908a5aac3 -r 2a45140bb5e1 web/edit_area_room.php
--- a/web/edit_area_room.php Sat Sep 17 10:26:42 2016 +0100
+++ b/web/edit_area_room.php Sat Sep 17 12:35:26 2016 +0100
@@ -518,12 +518,12 @@
}
// Acquire a mutex to lock out others who might be deleting the new area
- if (!sql_mutex_lock("$tbl_area"))
+ if (!sql_mutex_lock($tbl_area))
{
fatal_error(TRUE, get_vocab("failed_to_acquire"));
}
// Check the new area still exists
- if (sql_query1("SELECT COUNT(*) FROM $tbl_area WHERE id=$new_area LIMIT
1") < 1)
+ if (sql_query1("SELECT COUNT(*) FROM $tbl_area WHERE id=? LIMIT 1",
array($new_area)) < 1)
{
$valid_area = FALSE;
}
@@ -531,13 +531,13 @@
// (only do this if you're changing the room name or the area - if you're
// just editing the other details for an existing room we don't want to
reject
// the edit because the room already exists!)
- // [SQL escaping done by sql_syntax_casesensitive_equals()]
+ // [sql_syntax_casesensitive_equals() inserts a param placeholder, so we
must pass the param to sql_query1()]
elseif ( (($new_area != $old_area) || ($room_name != $old_room_name))
&& sql_query1("SELECT COUNT(*)
FROM $tbl_room
WHERE" .
sql_syntax_casesensitive_equals("room_name", $room_name) . "
AND area_id=$new_area
- LIMIT 1") > 0)
+ LIMIT 1", array($room_name)) > 0)
{
$valid_room_name = FALSE;
}
@@ -548,6 +548,7 @@
$room_disabled = (!empty($room_disabled)) ? 1 : 0;
$sql = "UPDATE $tbl_room SET ";
$n_fields = count($fields);
+ $sql_params = array();
$assign_array = array();
foreach ($fields as $field)
{
@@ -557,28 +558,36 @@
{
// first of all deal with the standard MRBS fields
case 'area_id':
- $assign_array[] = "area_id=$new_area";
+ $assign_array[] = "area_id=?";
+ $sql_params[] = $new_area;
break;
case 'disabled':
- $assign_array[] = "disabled=$room_disabled";
+ $assign_array[] = "disabled=?";
+ $sql_params[] = $room_disabled;
break;
case 'room_name':
- $assign_array[] = "room_name='" . sql_escape($room_name) . "'";
+ $assign_array[] = "room_name=?";
+ $sql_params[] =$room_name;
break;
case 'sort_key':
- $assign_array[] = "sort_key='" . sql_escape($sort_key) . "'";
+ $assign_array[] = "sort_key=?";
+ $sql_params[] = $sort_key;
break;
case 'description':
- $assign_array[] = "description='" . sql_escape($description) .
"'";
+ $assign_array[] = "description=?";
+ $sql_params[] = $description;
break;
case 'capacity':
- $assign_array[] = "capacity=$capacity";
+ $assign_array[] = "capacity=?";
+ $sql_params[] = $capacity;
break;
case 'room_admin_email':
- $assign_array[] = "room_admin_email='" .
sql_escape($room_admin_email) . "'";
+ $assign_array[] = "room_admin_email=?";
+ $sql_params[] = $room_admin_email;
break;
case 'custom_html':
- $assign_array[] = "custom_html='" . sql_escape($custom_html) .
"'";
+ $assign_array[] = "custom_html=?";
+ $sql_params[] = $custom_html;
break;
// then look at any user defined fields
default:
@@ -591,21 +600,23 @@
// Try and set it to NULL when we can because there will
be cases when we
// want to distinguish between NULL and 0 - especially
when the field
// is a genuine integer.
- $$var = ($field['is_nullable']) ? 'NULL' : 0;
+ $$var = ($field['is_nullable']) ? null : 0;
}
break;
default:
- $$var = "'" . sql_escape($$var) . "'";
+ // Do nothing
break;
}
- $assign_array[] = sql_quote($field['name']) . "=" . $$var;
+ $assign_array[] = sql_quote($field['name']) . "=?";
+ $sql_params[] = $$var;
break;
}
}
}
- $sql .= implode(",", $assign_array) . " WHERE id=$room";
- if (sql_command($sql) < 0)
+ $sql .= implode(",", $assign_array) . " WHERE id=?";
+ $sql_params[] = $room;
------------------------------------------------------------------------------
_______________________________________________
Mrbs-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mrbs-commits
