[Summary view] [Print] [Text view]

   1  <?php
   2  
   3  /// $Id $
   4  
   5  ///////////////////////////////////////////////////////////////////////////
   6  //                                                                       //
   7  // NOTICE OF COPYRIGHT                                                   //
   8  //                                                                       //
   9  // ADOdb  - Database Abstraction Library for PHP                         //
  10  //          http://adodb.sourceforge.net/                                //
  11  //                                                                       //
  12  // Copyright (c) 2000-2014 John Lim (jlim\@natsoft.com.my)               //
  13  //          All rights reserved.                                         //
  14  //          Released under both BSD license and LGPL library license.    //
  15  //          Whenever there is any discrepancy between the two licenses,  //
  16  //          the BSD license will take precedence                         //
  17  //                                                                       //
  18  // Moodle - Modular Object-Oriented Dynamic Learning Environment         //
  19  //          http://moodle.com                                            //
  20  //                                                                       //
  21  // Copyright (C) 2001-3001 Martin Dougiamas        http://dougiamas.com  //
  22  //           (C) 2001-3001 Eloy Lafuente (stronk7) http://contiento.com  //
  23  //                                                                       //
  24  // This program is free software; you can redistribute it and/or modify  //
  25  // it under the terms of the GNU General Public License as published by  //
  26  // the Free Software Foundation; either version 2 of the License, or     //
  27  // (at your option) any later version.                                   //
  28  //                                                                       //
  29  // This program is distributed in the hope that it will be useful,       //
  30  // but WITHOUT ANY WARRANTY; without even the implied warranty of        //
  31  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         //
  32  // GNU General Public License for more details:                          //
  33  //                                                                       //
  34  //          http://www.gnu.org/copyleft/gpl.html                         //
  35  //                                                                       //
  36  ///////////////////////////////////////////////////////////////////////////
  37  
  38  /**
  39  *  MSSQL Driver with auto-prepended "N" for correct unicode storage
  40  *  of SQL literal strings. Intended to be used with MSSQL drivers that
  41  *  are sending UCS-2 data to MSSQL (FreeTDS and ODBTP) in order to get
  42  *  true cross-db compatibility from the application point of view.
  43  */
  44  
  45  // security - hide paths
  46  if (!defined('ADODB_DIR')) die();
  47  
  48  // one useful constant
  49  if (!defined('SINGLEQUOTE')) define('SINGLEQUOTE', "'");
  50  
  51  include_once(ADODB_DIR.'/drivers/adodb-mssql.inc.php');
  52  
  53  class ADODB_mssql_n extends ADODB_mssql {
  54      var $databaseType = "mssql_n";
  55  
  56  	function _query($sql,$inputarr=false)
  57      {
  58          $sql = $this->_appendN($sql);
  59          return ADODB_mssql::_query($sql,$inputarr);
  60      }
  61  
  62           /**
  63       * This function will intercept all the literals used in the SQL, prepending the "N" char to them
  64       * in order to allow mssql to store properly data sent in the correct UCS-2 encoding (by freeTDS
  65       * and ODBTP) keeping SQL compatibility at ADOdb level (instead of hacking every project to add
  66       * the "N" notation when working against MSSQL.
  67       *
  68       * The orginal note indicated that this hack should only be used if ALL the char-based columns 
  69       * in your DB are of type nchar, nvarchar and ntext, but testing seems to indicate that SQL server
  70       * doesn't seem to care if the statement is used against char etc fields.
  71       *
  72       * @todo This function should raise an ADOdb error if one of the transformations fail
  73       * 
  74       * @param mixed $inboundData Either a string containing an SQL statement
  75       *                           or an array with resources from prepared statements
  76       *
  77       * @return mixed
  78       */
  79      function _appendN($inboundData) {
  80  
  81          $inboundIsArray  = false;
  82         
  83          if (is_array($inboundData))
  84          {
  85              $inboundIsArray = true;
  86              $inboundArray   = $inboundData;
  87          } else
  88              $inboundArray = (array)$inboundData;
  89          
  90          /*
  91           * All changes will be placed here
  92           */
  93          $outboundArray = $inboundArray;
  94          
  95          foreach($inboundArray as $inboundKey=>$inboundValue)
  96          {
  97          
  98              if (is_resource($inboundValue))
  99              {
 100                  /*
 101                  * Prepared statement resource
 102                  */
 103                  if ($this->debug)
 104                      ADOConnection::outp("{$this->databaseType} index $inboundKey value is resource, continue");
 105  
 106                  continue;
 107              }
 108             
 109              if (strpos($inboundValue, SINGLEQUOTE) === false)
 110              {
 111                  /*
 112                  * Check we have something to manipulate
 113                  */
 114                  if ($this->debug)
 115                      ADOConnection::outp("{$this->databaseType} index $inboundKey value $inboundValue has no single quotes, continue");
 116                  continue;
 117              }
 118  
 119              /*
 120              * Check we haven't an odd number of single quotes (this can cause problems below
 121              * and should be considered one wrong SQL). Exit with debug info.
 122              */
 123              if ((substr_count($inboundValue, SINGLEQUOTE) & 1)) 
 124              {
 125                  if ($this->debug)
 126                      ADOConnection::outp("{$this->databaseType} internal transformation: not converted. Wrong number of quotes (odd)");
 127                 
 128                  break;
 129              }
 130  
 131              /*
 132              * Check we haven't any backslash + single quote combination. It should mean wrong
 133              *  backslashes use (bad magic_quotes_sybase?). Exit with debug info.
 134              */
 135              $regexp = '/(\\\\' . SINGLEQUOTE . '[^' . SINGLEQUOTE . '])/';
 136              if (preg_match($regexp, $inboundValue))
 137              {
 138                  if ($this->debug) 
 139                      ADOConnection::outp("{$this->databaseType} internal transformation: not converted. Found bad use of backslash + single quote");
 140                  
 141                  break;
 142              }
 143  
 144              /*
 145              * Remove pairs of single-quotes
 146              */
 147              $pairs = array();
 148              $regexp = '/(' . SINGLEQUOTE . SINGLEQUOTE . ')/';
 149              preg_match_all($regexp, $inboundValue, $list_of_pairs);
 150              
 151              if ($list_of_pairs)
 152              {
 153                  foreach (array_unique($list_of_pairs[0]) as $key=>$value)
 154                      $pairs['<@#@#@PAIR-'.$key.'@#@#@>'] = $value;
 155                  
 156                  
 157                  if (!empty($pairs))
 158                      $inboundValue = str_replace($pairs, array_keys($pairs), $inboundValue);
 159                  
 160              }
 161  
 162              /*
 163              * Remove the rest of literals present in the query
 164              */
 165              $literals = array();
 166              $regexp = '/(N?' . SINGLEQUOTE . '.*?' . SINGLEQUOTE . ')/is';
 167              preg_match_all($regexp, $inboundValue, $list_of_literals);
 168             
 169             if ($list_of_literals)
 170             {
 171                  foreach (array_unique($list_of_literals[0]) as $key=>$value)
 172                      $literals['<#@#@#LITERAL-'.$key.'#@#@#>'] = $value;
 173                  
 174                 
 175                  if (!empty($literals))
 176                      $inboundValue = str_replace($literals, array_keys($literals), $inboundValue);
 177              }
 178  
 179              /*
 180              * Analyse literals to prepend the N char to them if their contents aren't numeric
 181              */
 182              if (!empty($literals))
 183              {
 184                  foreach ($literals as $key=>$value) {
 185                      if (!is_numeric(trim($value, SINGLEQUOTE)))
 186                          /*
 187                          * Non numeric string, prepend our dear N, whilst 
 188                          * Trimming potentially existing previous "N"
 189                          */
 190                          $literals[$key] = 'N' . trim($value, 'N'); 
 191                      
 192                  }
 193              }
 194  
 195              /*
 196              * Re-apply literals to the text
 197              */
 198              if (!empty($literals))
 199                  $inboundValue = str_replace(array_keys($literals), $literals, $inboundValue);
 200              
 201  
 202              /*
 203              * Any pairs followed by N' must be switched to N' followed by those pairs
 204              * (or strings beginning with single quotes will fail)
 205              */
 206              $inboundValue = preg_replace("/((<@#@#@PAIR-(\d+)@#@#@>)+)N'/", "N'$1", $inboundValue);
 207  
 208              /*
 209              * Re-apply pairs of single-quotes to the text
 210              */
 211              if (!empty($pairs))
 212                  $inboundValue = str_replace(array_keys($pairs), $pairs, $inboundValue);
 213              
 214  
 215              /*
 216              * Print transformation if debug = on
 217              */
 218              if (strcmp($inboundValue,$inboundArray[$inboundKey]) <> 0 && $this->debug)
 219                  ADOConnection::outp("{$this->databaseType} internal transformation: {$inboundArray[$inboundKey]} to {$inboundValue}");
 220              
 221              if (strcmp($inboundValue,$inboundArray[$inboundKey]) <> 0)
 222                  /*
 223                  * Place the transformed value into the outbound array
 224                  */
 225                  $outboundArray[$inboundKey] = $inboundValue;
 226          }
 227          
 228          /*
 229           * Any transformations are in the $outboundArray
 230           */
 231          if ($inboundIsArray)
 232              return $outboundArray;
 233          
 234          /*
 235           * We passed a string in originally
 236           */
 237          return $outboundArray[0];
 238          
 239      }
 240  
 241  }
 242  
 243  class ADORecordset_mssql_n extends ADORecordset_mssql {
 244      var $databaseType = "mssql_n";
 245  	function __construct($id,$mode=false)
 246      {
 247          parent::__construct($id,$mode);
 248      }
 249  }