From ed4b4bf1d03416febe96f27251e2abe2368c4584 Mon Sep 17 00:00:00 2001 From: stronk7 Date: Thu, 19 Oct 2006 21:57:23 +0000 Subject: [PATCH] New ADOdb driver that extends the standard mssql one. All it does is to prepend the 'N' char before any SQL literal sent to DB, mainly because nobody (ODBTP, FreeTDS, ODBC, SQL*Server) seems to be doing this task, and it prevents, in an high degree true cross-db compatibility. Once we've moved to prepared statements and some of the developers for any of the products above (hopefully MS, at server level) will avoid the 'N', we'll be able to go back to another mssql drivers. More info at: - http://docs.moodle.org/en/XMLDB_Problems (point 8) - http://tracker.moodle.org/browse/MDL-6877 --- lib/adodb/drivers/adodb-mssql_n.inc.php | 164 ++++++++++++++++++++++++ 1 file changed, 164 insertions(+) create mode 100644 lib/adodb/drivers/adodb-mssql_n.inc.php diff --git a/lib/adodb/drivers/adodb-mssql_n.inc.php b/lib/adodb/drivers/adodb-mssql_n.inc.php new file mode 100644 index 0000000000..bbadc5a0a9 --- /dev/null +++ b/lib/adodb/drivers/adodb-mssql_n.inc.php @@ -0,0 +1,164 @@ +_appendN($sql); + return ADODB_mssql::_query($sql,$inputarr); + } + + /** + * This function will intercept all the literals used in the SQL, prepending the "N" char to them + * in order to allow mssql to store properly data sent in the correct UCS-2 encoding (by freeTDS + * and ODBTP) keeping SQL compatibility at ADOdb level (instead of hacking every project to add + * the "N" notation when working against MSSQL. + * + * Note that this hack only must be used if ALL the char-based columns in your DB are of type nchar, + * nvarchar and ntext + */ + function _appendN($sql) { + + $result = $sql; + + /// Check we have some single quote in the query. Exit ok. + if (strpos($sql, SINGLEQUOTE) === false) { + return $sql; + } + + /// Check we haven't an odd number of single quotes (this can cause problems below + /// and should be considered one wrong SQL). Exit with debug info. + if ((substr_count($sql, SINGLEQUOTE) & 1)) { + if ($this->debug) { + ADOConnection::outp("{$this->databaseType} internal transformation: not converted. Wrong number of quotes (odd)"); + } + return $sql; + } + + /// Check we haven't any backslash + single quote combination. It should mean wrong + /// backslashes use (bad magic_quotes_sybase?). Exit with debug info. + $regexp = '/(\\\\' . SINGLEQUOTE . '[^' . SINGLEQUOTE . '])/'; + if (preg_match($regexp, $sql)) { + if ($this->debug) { + ADOConnection::outp("{$this->databaseType} internal transformation: not converted. Found bad use of backslash + single quote"); + } + return $sql; + } + + /// Remove pairs of single-quotes + $pairs = array(); + $regexp = '/(' . SINGLEQUOTE . SINGLEQUOTE . ')/'; + preg_match_all($regexp, $result, $list_of_pairs); + if ($list_of_pairs) { + foreach (array_unique($list_of_pairs[0]) as $key=>$value) { + $pairs['<@#@#@PAIR-'.$key.'@#@#@>'] = $value; + } + if (!empty($pairs)) { + $result = str_replace($pairs, array_keys($pairs), $result); + } + } + + /// Remove the rest of literals present in the query + $literals = array(); + $regexp = '/(N?' . SINGLEQUOTE . '.*?' . SINGLEQUOTE . ')/is'; + preg_match_all($regexp, $result, $list_of_literals); + if ($list_of_literals) { + foreach (array_unique($list_of_literals[0]) as $key=>$value) { + $literals['<#@#@#LITERAL-'.$key.'#@#@#>'] = $value; + } + if (!empty($literals)) { + $result = str_replace($literals, array_keys($literals), $result); + } + } + + /// Analyse literals to prepend the N char to them if their contents aren't numeric + if (!empty($literals)) { + foreach ($literals as $key=>$value) { + if (!is_numeric(trim($value, SINGLEQUOTE))) { + /// Non numeric string, prepend our dear N + $literals[$key] = 'N' . trim($value, 'N'); //Trimming potentially existing previous "N" + } + } + } + + /// Re-apply literals to the text + if (!empty($literals)) { + $result = str_replace(array_keys($literals), $literals, $result); + } + + /// Re-apply pairs of single-quotes to the text + if (!empty($pairs)) { + $result = str_replace(array_keys($pairs), $pairs, $result); + } + + /// Print transformation if debug = on + if ($result != $sql && $this->debug) { + ADOConnection::outp("{$this->databaseType} internal transformation:
{$sql}
to
{$result}"); + } + + return $result; + } +} + +class ADORecordset_mssql_n extends ADORecordset_mssql { + var $databaseType = "mssql_n"; + function ADORecordset_mssql_n($id,$mode=false) + { + $this->ADORecordset_mssql($id,$mode); + } +} +?> -- 2.39.5