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