Changeset:
66a6e5425b3a
https://sourceforge.net/p/mrbs/hg-code/ci/66a6e5425b3a014ac38fb796ac84971e3b452bfc
Author:
John Beranek <[email protected]>
Date:
Sat Oct 08 20:52:16 2016 +0100
Log message:
Merged the 'pdo' branch, which changes MRBS to utilise PHP's
PDO mechanism and use a DB class to perform all SQL. SQL
statements are now parameterised, and not just escaped in
MRBS code.
diffstat:
INSTALL | 2 +-
README.sqlapi | 244 +++++-----
convert_db_to_utf8.php | 76 +-
web/Themes/default/header.inc | 428 ++++++++++---------
web/admin.php | 33 +-
web/auth/auth_db.inc | 32 +-
web/auth/auth_db_ext.inc | 154 ++----
web/auth/auth_ldap.inc | 4 +-
web/check_slot_ajax.php | 1 +
web/config.inc.php | 6 +-
web/css/mrbs-print.css.php | 4 +-
web/css/mrbs-rtl.css.php | 6 +-
web/css/mrbs.css.php | 91 +++-
web/day.php | 1 -
web/dbsys.inc | 833 +------------------------------------
web/defaultincludes.inc | 1 +
web/del.php | 47 +-
web/del_entry_ajax.php | 4 +-
web/edit_area_room.php | 196 +++++---
web/edit_entry.php | 133 ++---
web/edit_entry_handler.php | 21 +-
web/edit_users.php | 102 +--
web/functions.inc | 285 +++++++-----
web/functions_error.inc | 108 ++++
web/functions_ical.inc | 70 +--
web/functions_mail.inc | 10 +-
web/functions_table.inc | 5 -
web/functions_view.inc | 2 +-
web/help.php | 2 +-
web/import.php | 29 +-
web/internalconfig.inc.php | 11 +-
web/js.inc | 4 +
web/lang/lang.en | 1 +
web/lib/MRBS/DB.php | 233 ++++++++++
web/lib/MRBS/DBException.php | 16 +
web/lib/MRBS/DBFactory.php | 27 +
web/lib/MRBS/DBStatement.php | 80 +++
web/lib/MRBS/DB_mysql.php | 288 +++++++++++++
web/lib/MRBS/DB_pgsql.php | 302 +++++++++++++
web/mincals.inc | 10 +-
web/mrbs_auth.inc | 6 +-
web/mrbs_sql.inc | 509 ++++++++++------------
web/mysqli.inc | 638 -----------------------------
web/pending.php | 19 +-
web/pgsql.inc | 651 -----------------------------
web/report.php | 68 +-
web/search.php | 74 +-
web/session/session_cookie.inc | 16 +-
web/session/session_http.inc | 2 +-
web/session/session_php.inc | 6 +-
web/session/session_wordpress.inc | 4 +-
web/systemdefaults.inc.php | 15 +-
web/upgrade.inc | 20 +-
web/upgrade/13/post.inc | 10 +-
web/upgrade/15/post.inc | 19 +-
web/upgrade/16/post.inc | 7 +-
web/upgrade/17/post.inc | 53 +-
web/upgrade/2/post.inc | 9 +-
web/upgrade/21/post.inc | 19 +-
web/upgrade/24/post.inc | 49 +-
web/upgrade/32/post.inc | 20 +-
web/upgrade/34/post.inc | 24 +-
web/upgrade/4/post.inc | 10 +-
web/upgrade/43/post.inc | 10 +-
web/upgrade/47/post.inc | 50 +-
web/upgrade/5/post.inc | 20 +-
web/upgrade/6/post.inc | 33 +-
web/upgrade/7/post.inc | 22 +-
web/upgrade/8/post.inc | 8 +-
web/view_entry.php | 24 +-
70 files changed, 2569 insertions(+), 3748 deletions(-)
diffs (truncated from 9213 to 300 lines):
diff -r 9b3d1f4083d5 -r 66a6e5425b3a INSTALL
--- a/INSTALL Mon Oct 03 17:30:09 2016 +0100
+++ b/INSTALL Sat Oct 08 20:52:16 2016 +0100
@@ -354,7 +354,7 @@
First, select your database system. Define one of the following:
- $dbsys = "mysqli";
+ $dbsys = "mysql";
$dbsys = "pgsql";
Then define your database connection parameters. Set the values for:
diff -r 9b3d1f4083d5 -r 66a6e5425b3a README.sqlapi
--- a/README.sqlapi Mon Oct 03 17:30:09 2016 +0100
+++ b/README.sqlapi Sat Oct 08 20:52:16 2016 +0100
@@ -1,14 +1,15 @@
-README.sqlapi - Database wrappers for MRBS
+README.sqlapi - Database abstraction class for MRBS
-----------------------------------------------------------------------------
-This is a simple set of PHP database call wrapper routines, currently
-implemented for MySQL (>= 5.1) and PostgreSQL (>= 8.2). It was written
-for MRBS but may be useful in other applications.
+MRBS utilises a database abstraction class, currently implemented for
+MySQL (>= 5.1) and PostgreSQL (>= 8.2). It was written for MRBS but may
+be useful in other applications.
-This package supports multiple connections to arbitrary databases,
-but also handles the default MRBS database connection without the user
-always passing a database connection handle around by storing this
-default connection handle in a global variable inside the database
-abstraction. It supports multiple pending results for each connection. It
+The class supports multiple connections to arbitrary databases,
+but there is also a simple wrapper function to allow use of the default
+MRBS database without the user always passing a database object
+around. This is the function "db()" defined in dbsys.inc.
+
+The class supports multiple pending results for each connection. It
can be configured to use PHP persistent (pooled) database connections,
or normal (single use) connections.
@@ -20,20 +21,20 @@
combinations of user/password/database) implemented on your site. Note that
the default for PostgreSQL is a maximum of 32 connections, and the default
for Apache MaxClients is 150. If you want to use persistent connections,
-see the $persist parameter to sql_connect() below.
+see the $persist parameter to DBFactory::create() below.
-----------------------------------------------------------------------------
To use this package, include "dbsys.inc" after defining the following
variables:
- $dbsys = The database abstraction to use, 'mysqli' or 'pgsql'
+ $dbsys = The database abstraction to use, 'mysql' or 'pgsql'
$db_host = The hostname of the database server, or "localhost"
$db_login = The username to use when connecting to the database
$db_password = The database account password
$db_database = The database name
Optionally, you can define:
- $db_nopersist = 1;
-if you do not want to use persistent connections.
+ $db_persist = true;
+if you want to use persistent connections.
If using PostgreSQL, and the database server is on the same host as the web
server, you can specify $db_host="localhost" to use TCP, or $db_host="" to
@@ -41,16 +42,15 @@
your server runs without the -i option, it will only accept Unix Domain
Socket connections, so you must use $db_host="".
-After your script includes the file, it will be connected to the database,
-by calling the abstracted function sql_$dbsys_default_connect().
+After your script includes the file, you can get the default database
+connection object by calling db().
If an error occurs while trying to connect, a message will be output
-followed by a PHP exit. (Failure to connect to the database is the only
-reason the functions defined here will output an error message and exit.)
+followed by a PHP exit.
The way MRBS uses this is to define a configuration file config.inc.php with
the above variables plus:
- $dbsys = "pgsql"; // or: $dbsys = "mysqli";
+ $dbsys = "pgsql"; // or: $dbsys = "mysql";
Then, each PHP script which wants to connect to the database starts with:
include "config.inc.php";
include "dbsys.inc";
@@ -80,12 +80,6 @@
Non-standard features used, available in both PostgreSQL and MySQL (this
information is provided for anyone attempting to port MRBS to another
database system):
- + Escaping quoted strings. The SQL-92 standard says to double embedded
- single quotes, and that's the only escaping used or defined. So:
- insert into mytable values ('CAN''T');
- But both MySQL and PostgreSQL support backslash-escaping, which is
- easier to do in PHP, so I have not attempted to wrap database string
- quoting.
+ MySQL implicitly assigns "DEFAULT current_timestamp" to a timestamp
column; this must be done explicitly in other database systems.
+ The column called TIMESTAMP is not legal in SQL-92. It would be legal
@@ -108,74 +102,40 @@
-----------------------------------------------------------------------------
-The database-independent wrapper functions are documented here:
+The database class methods are documented here:
-Each of the functions below takes an optional final argument that is the
-MRBS database handle returned by sql_connect() call.
+To make a new connection to a database, use the method DBFactory::create(), as:
-sql_command($sql)
+DBFactory::create($db_system,
+ $db_host,
+ $db_username,
+ $db_password,
+ $db_name,
+ $persist = 0,
+ $db_port = null)
+ Here $db_system is either 'mysql' or 'pgsql' and $db_name is the name of
+ the database to access. This method returns an object of the class "DB".
+
+The "DB" class has the following object methods:
+
+->command($sql, $params)
Execute a non-SELECT SQL command (for example: insert, update, delete).
Returns the number of tuples affected if OK (a number >= 0).
- Returns -1 on error; use sql_error() to get the error message.
+ Raises a "DBException" exception on error.
-sql_query($sql)
- Execute an SQL query. Returns a database-dependent result handle, which
- should be passed back to sql_row() or sql_row_keyed() to get the results.
- If sql_row() or sql_row_keyed() isn't used to read all rows (plus 1), you
- must call sql_free() to free the result structure.
- Returns 0 on error; use sql_error() to get the error message.
+->query($sql, $params)
+ Execute an SQL query. Returns an object of class "DBStatement" (see methods
further below).
-sql_row($result, $rownumber)
- Return a row from a result. The first row is row number 0.
- The row is returned as an array with index 0=first column, etc.
- When called with i >= number of rows in the result, this frees up the
- results structure and returns 0. This is designed to be used in a loop
- like this to retrieve all the rows:
+->query1($sql, $params)
+ Execute an SQL query which should return a single non-negative number value.
+ Returns the value of the single column in the single row of the query
|
+ result or -1 if the query returns no result, or a single NULL value, such as
from
+ a MIN or MAX aggregate function applied over no rows.
+ Raises a "DBException" exception on error.
+ This is a short-cut alternative to ->query(), good for use with count(*)
+ and similar queries.
- for ($i = 0; (($row = sql_row($r, $i)); $i++) { ... process the row ... }
-
- When used this way, there is no need to call sql_free($result). But if
- rows are accessed in other way, sql_free() must be called when you are
- done with the result handle.
-
-sql_row_keyed($result, $rownumber)
- Return a row from a result. The first row is row number 0.
- The row is returned as an associative array with column (field) names as
- the indexes. (PHP also makes numeric indexes for the same data.)
- When called with i >= number of rows in the result, this frees up the
- results structure and returns 0. This is designed to be used in a loop
- like this to retrieve all the rows:
-
- for ($i = 0; (($row = sql_row_keyed($r, $i)); $i++) { ... }
-
- When used this way, there is no need to call sql_free($result). But if
- rows are accessed in other ways, sql_free() must be called when you are
- done with the result handle.
- NOTE: You should explicitly name each column in your SQL statement which
- is not a simple field name, because databases differ in how they assume
- a default name. For example, don't use sql_row_keyed() on a query
- like: SELECT name, COUNT(*) FROM ...
- Instead use: SELECT name, COUNT(*) AS totals FROM ...
- so you can reliably refer to the count as row["totals"].
-
-sql_count($result)
- Return the number of rows returned by a result handle from sql_query().
-
-sql_query1($sql)
- Execute an SQL query which should return a single non-negative number value.
- Returns the value of the single column in the single row of the query
- result, or -1 on error.
- This is a short-cut alternative to sql_query(), good for use with count(*)
- and similar queries. Error checking is limited (caller cannot tell if
- an error occurred or no values were returned), but it is easier to use.
-
-sql_free($result)
- Free a result handle $result, returned by a previous call to sql_query().
- You must call this if you do not use sql_row() or sql_row_keyed() to fetch
- all rows of the query result and then call it once more. See sql_row() for
- more information.
-
-sql_insert_id($table, $fieldname)
+->insert_id($table, $fieldname)
Return the value of an autoincrement/serial field from the last insert.
This must be called right after the insert on that table. The $fieldname
is the name of the autoincrement or serial field in the table. The
@@ -185,23 +145,23 @@
specified, and it must name the only autoincrement/serial field in the
row inserted by the most recent INSERT.
-sql_error()
- Return the text of the last error message. Only call this after an
- error return from sql_query() or sql_command() - it is not reliable
- as an indicator if an error did in fact occur.
+->error()
+ Return the text of the last error message.
-sql_begin()
+->begin()
Begin a transaction, if the database supports it. This is used to
improve performance for multiple insert/delete/updates on databases
- which support transactions, and using it is not required. There is
- no support for rollback, because not all databases can do it. Do
- not attempt to have both sql_begin() and sql_mutex_lock() active since
+ which support transactions, and using it is not required. Do
+ not attempt to have both ->begin() and ->mutex_lock() active since
then both may be implemented with a shared underlying mechanism.
-sql_commit()
- Commit (end) a transaction. See sql_begin().
+->commit()
+ Commit (end) a transaction. See ->begin().
-sql_mutex_lock($name)
+->rollback()
+ Rollback a transaction. See ->begin().
+
+->mutex_lock($name)
Acquire a mutual-exclusion lock on the named table. For portability:
* This will not lock out SELECTs.
* It may lock out DELETE/UPDATE/INSERT or it may not.
@@ -216,51 +176,99 @@
automatically release the lock if the script exits. (With persistent
connections, the locks would not otherwise be released on exit, and a
deadlock will occur.)
- This call effectively calls sql_begin(), so do not use it inside an
- sql_begin()/sql_end() block, nor use sql_begin() between calls to
- sql_mutex_lock() and sql_mutex_unlock().
+ This call effectively calls ->begin(), so do not use it inside an
+ ->begin()/->end() block, nor use ->begin() between calls to
+ ->mutex_lock() and ->mutex_unlock().
-sql_mutex_unlock($name)
- Release a mutual-exclusion lock on the named table. See sql_mutex_lock().
- This also effectively calls sql_commit().
+->mutex_unlock($name)
+ Release a mutual-exclusion lock on the named table. See ->mutex_lock().
+ This also effectively calls ->commit().
-sql_version()
+->version()
Return a string identifying the database system and version.
-sql_connect($host, $username, $password, $db_name, $persist, $db_port)
- Connects to the specified database using the specified credentials,
- optionally using persistent database connections and a port number.
- Returns an MRBS specific database handle, which can be passed as the
- last argument of all the other sql_*() functions.
-
-----------------------------------------------------------------------------
-The following sql_syntax_* routines are intended to help you build up SQL
+The following ->syntax_* methods are intended to help you build up SQL
statements using non-standard features. Each returns a portion of SQL (with
leading and trailing spaces) which implements the named non-standard feature
-for the selected database.
+for the selected database. Some methods must also be passed (by reference) an
+array object for building the SQL parameters to pass to the query/command
method.
-sql_syntax_limit($count, $offset)
+->syntax_limit($count, $offset)
Generate non-standard SQL for LIMIT clauses, to make the query return
no more than $count records, starting at position $offset (basis 0).
-sql_syntax_timestamp_to_unix($fieldname)
+->syntax_timestamp_to_unix($fieldname)
Generate non-standard SQL to output a TIMESTAMP as a Unix time_t. The
argument must be the name of a timestamp field.
-sql_syntax_caseless_contains($fieldname, $s)
+->syntax_caseless_contains($fieldname, $s, &$params)
Generate a non-standard SQL predicate clause which will be true if the
string $s is contained anywhere in the named field, using case insensitive
string compare. This uses LIKE or Regular Expression matching, depending
- on the database system. It handles all quoting on the $s argument of the
- characters needed by the selected method, so don't call this with an
- argument which has already been "magic quoted".
+ on the database system. This method modifies the passed $params array
+ to add the appropriate SQL parameters.
+
+->syntax_casesensitive_equals($fieldname, $string, &$params)
+ Generates a non-standard SQL predicate clause for a case-sensitive equals.
+ This method modifies the passed $params array to add the appropriate
+ SQL parameters.
+
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
_______________________________________________
Mrbs-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mrbs-commits
