[Summary view]

Differences Between: [Versions 310 and 401] [Versions 311 and 401] [Versions 39 and 401]

   1  <?php
   2  /**
   3   * MSSQL Driver with auto-prepended "N" for correct unicode storage of SQL literal strings.
   4   *
   5   * Intended to be used with MSSQL drivers that are sending UCS-2 data to MSSQL
   6   * (FreeTDS and ODBTP) in order to get true cross-db compatibility from the
   7   * application point of view.
   8   *
   9   * This file is part of ADOdb, a Database Abstraction Layer library for PHP.
  10   *
  11   * @package ADOdb
  12   * @link https://adodb.org Project's web site and documentation
  13   * @link https://github.com/ADOdb/ADOdb Source code and issue tracker
  14   *
  15   * The ADOdb Library is dual-licensed, released under both the BSD 3-Clause
  16   * and the GNU Lesser General Public Licence (LGPL) v2.1 or, at your option,
  17   * any later version. This means you can use it in proprietary products.
  18   * See the LICENSE.md file distributed with this source code for details.
  19   * @license BSD-3-Clause
  20   * @license LGPL-2.1-or-later
  21   *
  22   * @copyright 2000-2013 John Lim
  23   * @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community
  24   */
  25  
  26  // security - hide paths
  27  if (!defined('ADODB_DIR')) die();
  28  
  29  // one useful constant
  30  if (!defined('SINGLEQUOTE')) define('SINGLEQUOTE', "'");
  31  
  32  include_once(ADODB_DIR.'/drivers/adodb-mssql.inc.php');
  33  
  34  class ADODB_mssql_n extends ADODB_mssql {
  35  	 var $databaseType = "mssql_n";
  36  
  37  	function _query($sql,$inputarr=false)
  38  	 {
  39          $sql = $this->_appendN($sql);
  40  	 	 return ADODB_mssql::_query($sql,$inputarr);
  41  	 }
  42  
  43  	 /**
  44       * This function will intercept all the literals used in the SQL, prepending the "N" char to them
  45       * in order to allow mssql to store properly data sent in the correct UCS-2 encoding (by freeTDS
  46       * and ODBTP) keeping SQL compatibility at ADOdb level (instead of hacking every project to add
  47       * the "N" notation when working against MSSQL.
  48       *
  49       * The original note indicated that this hack should only be used if ALL the char-based columns
  50       * in your DB are of type nchar, nvarchar and ntext, but testing seems to indicate that SQL server
  51       * doesn't seem to care if the statement is used against char etc fields.
  52       *
  53       * @todo This function should raise an ADOdb error if one of the transformations fail
  54       *
  55       * @param mixed $inboundData Either a string containing an SQL statement
  56       *                           or an array with resources from prepared statements
  57       *
  58       * @return mixed
  59       */
  60      function _appendN($inboundData) {
  61  
  62          $inboundIsArray  = false;
  63  
  64          if (is_array($inboundData))
  65          {
  66              $inboundIsArray = true;
  67              $inboundArray   = $inboundData;
  68          } else
  69              $inboundArray = (array)$inboundData;
  70  
  71          /*
  72           * All changes will be placed here
  73           */
  74          $outboundArray = $inboundArray;
  75  
  76          foreach($inboundArray as $inboundKey=>$inboundValue)
  77          {
  78  
  79              if (is_resource($inboundValue))
  80              {
  81                  /*
  82                  * Prepared statement resource
  83                  */
  84                  if ($this->debug)
  85                      ADOConnection::outp("{$this->databaseType} index $inboundKey value is resource, continue");
  86  
  87                  continue;
  88              }
  89  
  90              if (strpos($inboundValue, SINGLEQUOTE) === false)
  91              {
  92                  /*
  93                  * Check we have something to manipulate
  94                  */
  95                  if ($this->debug)
  96                      ADOConnection::outp("{$this->databaseType} index $inboundKey value $inboundValue has no single quotes, continue");
  97                  continue;
  98              }
  99  
 100              /*
 101              * Check we haven't an odd number of single quotes (this can cause problems below
 102              * and should be considered one wrong SQL). Exit with debug info.
 103              */
 104              if ((substr_count($inboundValue, SINGLEQUOTE) & 1))
 105              {
 106                  if ($this->debug)
 107                      ADOConnection::outp("{$this->databaseType} internal transformation: not converted. Wrong number of quotes (odd)");
 108  
 109                  break;
 110              }
 111  
 112              /*
 113              * Check we haven't any backslash + single quote combination. It should mean wrong
 114              *  backslashes use (bad magic_quotes_sybase?). Exit with debug info.
 115              */
 116              $regexp = '/(\\\\' . SINGLEQUOTE . '[^' . SINGLEQUOTE . '])/';
 117              if (preg_match($regexp, $inboundValue))
 118              {
 119                  if ($this->debug)
 120                      ADOConnection::outp("{$this->databaseType} internal transformation: not converted. Found bad use of backslash + single quote");
 121  
 122                  break;
 123              }
 124  
 125              /*
 126              * Remove pairs of single-quotes
 127              */
 128              $pairs = array();
 129              $regexp = '/(' . SINGLEQUOTE . SINGLEQUOTE . ')/';
 130              preg_match_all($regexp, $inboundValue, $list_of_pairs);
 131  
 132              if ($list_of_pairs)
 133              {
 134                  foreach (array_unique($list_of_pairs[0]) as $key=>$value)
 135                      $pairs['<@#@#@PAIR-'.$key.'@#@#@>'] = $value;
 136  
 137  
 138                  if (!empty($pairs))
 139                      $inboundValue = str_replace($pairs, array_keys($pairs), $inboundValue);
 140  
 141              }
 142  
 143              /*
 144              * Remove the rest of literals present in the query
 145              */
 146              $literals = array();
 147              $regexp = '/(N?' . SINGLEQUOTE . '.*?' . SINGLEQUOTE . ')/is';
 148              preg_match_all($regexp, $inboundValue, $list_of_literals);
 149  
 150             if ($list_of_literals)
 151             {
 152                  foreach (array_unique($list_of_literals[0]) as $key=>$value)
 153                      $literals['<#@#@#LITERAL-'.$key.'#@#@#>'] = $value;
 154  
 155  
 156                  if (!empty($literals))
 157                      $inboundValue = str_replace($literals, array_keys($literals), $inboundValue);
 158              }
 159  
 160              /*
 161              * Analyse literals to prepend the N char to them if their contents aren't numeric
 162              */
 163              if (!empty($literals))
 164              {
 165                  foreach ($literals as $key=>$value) {
 166                      if (!is_numeric(trim($value, SINGLEQUOTE)))
 167                          /*
 168                          * Non numeric string, prepend our dear N, whilst
 169                          * Trimming potentially existing previous "N"
 170                          */
 171                          $literals[$key] = 'N' . trim($value, 'N');
 172  
 173                  }
 174              }
 175  
 176              /*
 177              * Re-apply literals to the text
 178              */
 179              if (!empty($literals))
 180                  $inboundValue = str_replace(array_keys($literals), $literals, $inboundValue);
 181  
 182  
 183              /*
 184              * Any pairs followed by N' must be switched to N' followed by those pairs
 185              * (or strings beginning with single quotes will fail)
 186              */
 187              $inboundValue = preg_replace("/((<@#@#@PAIR-(\d+)@#@#@>)+)N'/", "N'$1", $inboundValue);
 188  
 189              /*
 190              * Re-apply pairs of single-quotes to the text
 191              */
 192              if (!empty($pairs))
 193                  $inboundValue = str_replace(array_keys($pairs), $pairs, $inboundValue);
 194  
 195  
 196              /*
 197              * Print transformation if debug = on
 198              */
 199              if (strcmp($inboundValue,$inboundArray[$inboundKey]) <> 0 && $this->debug)
 200                  ADOConnection::outp("{$this->databaseType} internal transformation: {$inboundArray[$inboundKey]} to {$inboundValue}");
 201  
 202              if (strcmp($inboundValue,$inboundArray[$inboundKey]) <> 0)
 203                  /*
 204                  * Place the transformed value into the outbound array
 205                  */
 206                  $outboundArray[$inboundKey] = $inboundValue;
 207          }
 208  
 209          /*
 210           * Any transformations are in the $outboundArray
 211           */
 212          if ($inboundIsArray)
 213              return $outboundArray;
 214  
 215          /*
 216           * We passed a string in originally
 217           */
 218          return $outboundArray[0];
 219  
 220      }
 221  
 222  }
 223  
 224  class ADORecordset_mssql_n extends ADORecordset_mssql {
 225  	 var $databaseType = "mssql_n";
 226  }