Changeset:
50705e14cc81
https://sourceforge.net/p/mrbs/hg-code/ci/50705e14cc8157752a7902f0f0d69e7931ad6cf1
Author:
John Beranek <[email protected]>
Date:
Sun Sep 18 10:05:15 2016 +0100
Log message:
Modified sql_syntax_caseless_contains() to no longer perform
escaping, and instead to modify the caller's SQL params array
instead.
Updated README.sqlapi a bit, still needs more work to describe
the new parameterised calls.
diffstat:
README.sqlapi | 8 ++++----
web/dbsys.inc | 8 ++++----
web/mysqli.inc | 18 ++++++++++++------
web/pgsql.inc | 12 ++++++++----
web/report.php | 8 ++++----
web/search.php | 10 +++++-----
6 files changed, 37 insertions(+), 27 deletions(-)
diffs (178 lines):
diff -r f91156d63be0 -r 50705e14cc81 README.sqlapi
--- a/README.sqlapi Sun Sep 18 09:45:49 2016 +0100
+++ b/README.sqlapi Sun Sep 18 10:05:15 2016 +0100
@@ -113,12 +113,12 @@
Each of the functions below takes an optional final argument that is the
MRBS database handle returned by sql_connect() call.
-sql_command($sql)
+sql_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.
-sql_query($sql)
+sql_query($sql, $params)
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
@@ -161,7 +161,7 @@
sql_count($result)
Return the number of rows returned by a result handle from sql_query().
-sql_query1($sql)
+sql_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 on error.
@@ -248,7 +248,7 @@
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)
+sql_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
diff -r f91156d63be0 -r 50705e14cc81 web/dbsys.inc
--- a/web/dbsys.inc Sun Sep 18 09:45:49 2016 +0100
+++ b/web/dbsys.inc Sun Sep 18 10:05:15 2016 +0100
@@ -523,11 +523,11 @@
// Returns the syntax for a caseless "contains" function
-function sql_syntax_caseless_contains($fieldname, $s)
+function sql_syntax_caseless_contains($fieldname, $string, &$params)
{
- if (func_num_args() > 2)
+ if (func_num_args() > 3)
{
- $handle = func_get_arg(2);
+ $handle = func_get_arg(3);
$db_sys = $handle['system'];
$db_conn = $handle['connection'];
}
@@ -540,7 +540,7 @@
}
$f = __NAMESPACE__ . "\\sql_${db_sys}_syntax_caseless_contains";
- return $f($fieldname, $s, $db_conn);
+ return $f($fieldname, $string, $params, $db_conn);
}
// Returns the syntax for adding a column after another column, in an
diff -r f91156d63be0 -r 50705e14cc81 web/mysqli.inc
--- a/web/mysqli.inc Sun Sep 18 09:45:49 2016 +0100
+++ b/web/mysqli.inc Sun Sep 18 10:05:15 2016 +0100
@@ -346,17 +346,23 @@
// Generate non-standard SQL to match a string anywhere in a field's value
// in a case insensitive manner. $s is the un-escaped/un-slashed string.
+//
+// Also takes a required pass-by-reference parameter to modify the SQL
+// parameters appropriately.
+//
// In MySQL, REGEXP seems to be case sensitive, so use LIKE instead. But this
// requires quoting of % and _ in addition to the usual.
-function sql_mysqli_syntax_caseless_contains($fieldname, $s, $db_conn = null)
+function sql_mysqli_syntax_caseless_contains($fieldname, $string, &$params,
$db_conn = null)
{
sql_mysqli_ensure_handle($db_conn);
- $s = str_replace("\\", "\\\\", $s);
- $s = str_replace("%", "\\%", $s);
- $s = str_replace("_", "\\_", $s);
- $s = str_replace("'", "''", $s);
- return " $fieldname LIKE '%$s%' ";
+ $string = str_replace("\\", "\\\\", $string);
+ $string = str_replace("%", "\\%", $string);
+ $string = str_replace("_", "\\_", $string);
+
+ $params[] = "%$string%";
+
+ return " $fieldname LIKE ? ";
}
diff -r f91156d63be0 -r 50705e14cc81 web/pgsql.inc
--- a/web/pgsql.inc Sun Sep 18 09:45:49 2016 +0100
+++ b/web/pgsql.inc Sun Sep 18 10:05:15 2016 +0100
@@ -388,16 +388,20 @@
// Generate non-standard SQL to match a string anywhere in a field's value
// in a case insensitive manner. $s is the un-escaped/un-slashed string.
+//
+// Also takes a required pass-by-reference parameter to modify the SQL
+// parameters appropriately.
+//
// In PostgreSQL, we can do case insensitive regexp with ~*, but not case
// insensitive LIKE matching.
// Quotemeta escapes everything we need except for single quotes.
-function sql_pgsql_syntax_caseless_contains($fieldname, $s, $db_conn = null)
+function sql_pgsql_syntax_caseless_contains($fieldname, $string, &$params,
$db_conn = null)
{
sql_pgsql_ensure_handle($db_conn);
- $s = quotemeta($s);
- $s = str_replace("'", "''", $s);
- return " $fieldname ~* '$s' ";
+ $sql_params[] = quotemeta($string);
+
+ return " $fieldname ~* ? ";
}
diff -r f91156d63be0 -r 50705e14cc81 web/report.php
--- a/web/report.php Sun Sep 18 09:45:49 2016 +0100
+++ b/web/report.php Sun Sep 18 10:05:15 2016 +0100
@@ -1154,8 +1154,8 @@
// bother with complicated things such as custom fields or select_options
if ($table != 'entry')
{
- // sql_syntax_caseless_contains() does the SQL escaping
- $sql .= " AND" . sql_syntax_caseless_contains("$full_column_name", $match);
+ // sql_syntax_caseless_contains() modifies the SQL params array too
+ $sql .= " AND" . sql_syntax_caseless_contains("$full_column_name", $match,
$sql_params);
return $sql;
}
@@ -1208,8 +1208,8 @@
// (4) Strings
else
{
- // sql_syntax_caseless_contains() does the SQL escaping
- $sql .= " AND" . sql_syntax_caseless_contains("$full_column_name", $match);
+ // sql_syntax_caseless_contains() modifies the SQL params array too
+ $sql .= " AND" . sql_syntax_caseless_contains("$full_column_name", $match,
$sql_params);
}
return $sql;
diff -r f91156d63be0 -r 50705e14cc81 web/search.php
--- a/web/search.php Sun Sep 18 09:45:49 2016 +0100
+++ b/web/search.php Sun Sep 18 10:05:15 2016 +0100
@@ -194,12 +194,12 @@
$now = mktime(0, 0, 0, $month, $day, $year);
// This is the main part of the query predicate, used in both queries:
-// NOTE: sql_syntax_caseless_contains() does the SQL escaping
+// NOTE: sql_syntax_caseless_contains() modifies our SQL params for us
$sql_params = array();
-$sql_pred = "( " . sql_syntax_caseless_contains("E.create_by", $search_str)
- . " OR " . sql_syntax_caseless_contains("E.name", $search_str)
- . " OR " . sql_syntax_caseless_contains("E.description", $search_str);
+$sql_pred = "( " . sql_syntax_caseless_contains("E.create_by", $search_str,
$sql_params)
+ . " OR " . sql_syntax_caseless_contains("E.name", $search_str, $sql_params)
+ . " OR " . sql_syntax_caseless_contains("E.description", $search_str,
$sql_params);
// Also need to search custom fields (but only those with character data,
// which can include fields that have an associative array of options)
@@ -226,7 +226,7 @@
}
elseif ($field['nature'] == 'character')
{
- $sql_pred .= " OR " . sql_syntax_caseless_contains("E." .
sql_quote($field['name']), $search_str);
+ $sql_pred .= " OR " . sql_syntax_caseless_contains("E." .
sql_quote($field['name']), $search_str, $sql_params);
}
}
}
------------------------------------------------------------------------------
_______________________________________________
Mrbs-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mrbs-commits
