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