Changeset:
        2a45140bb5e1
        
https://sourceforge.net/p/mrbs/hg-code/ci/2a45140bb5e156478c2ef721fc07977d036fc835
Author:
        John Beranek <jbera...@users.sourceforge.net>
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
Mrbs-commits@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mrbs-commits

Reply via email to