1: <?php
  2: 
  3: /**
  4:  * This file is part of the Nette Framework (https://nette.org)
  5:  * Copyright (c) 2004 David Grudl (https://davidgrudl.com)
  6:  */
  7: 
  8: namespace Nette\Database;
  9: 
 10: use Nette;
 11: 
 12: 
 13: /**
 14:  * SQL preprocessor.
 15:  *
 16:  * @author     David Grudl
 17:  */
 18: class SqlPreprocessor extends Nette\Object
 19: {
 20:     /** @var Connection */
 21:     private $connection;
 22: 
 23:     /** @var ISupplementalDriver */
 24:     private $driver;
 25: 
 26:     /** @var array of input parameters */
 27:     private $params;
 28: 
 29:     /** @var array of parameters to be processed by PDO */
 30:     private $remaining;
 31: 
 32:     /** @var int */
 33:     private $counter;
 34: 
 35:     /** @var string values|assoc|multi|select|union */
 36:     private $arrayMode;
 37: 
 38:     /** @var array */
 39:     private $arrayModes;
 40: 
 41: 
 42:     public function __construct(Connection $connection)
 43:     {
 44:         $this->connection = $connection;
 45:         $this->driver = $connection->getSupplementalDriver();
 46:         $this->arrayModes = array(
 47:             'INSERT' => $this->driver->isSupported(ISupplementalDriver::SUPPORT_MULTI_INSERT_AS_SELECT) ? 'select' : 'values',
 48:             'REPLACE' => 'values',
 49:             'UPDATE' => 'assoc',
 50:             'WHERE' => 'and',
 51:             'HAVING' => 'and',
 52:             'ORDER BY' => 'order',
 53:             'GROUP BY' => 'order',
 54:         );
 55:     }
 56: 
 57: 
 58:     /**
 59:      * @param  array
 60:      * @return array of [sql, params]
 61:      */
 62:     public function process($params)
 63:     {
 64:         $this->params = $params;
 65:         $this->counter = 0;
 66:         $this->remaining = array();
 67:         $this->arrayMode = 'assoc';
 68:         $res = array();
 69: 
 70:         while ($this->counter < count($params)) {
 71:             $param = $params[$this->counter++];
 72: 
 73:             if (($this->counter === 2 && count($params) === 2) || !is_scalar($param)) {
 74:                 $res[] = $this->formatValue($param);
 75:             } else {
 76:                 $res[] = Nette\Utils\Strings::replace(
 77:                     $param,
 78:                     '~\'.*?\'|".*?"|\?|\b(?:INSERT|REPLACE|UPDATE|WHERE|HAVING|ORDER BY|GROUP BY)\b|/\*.*?\*/|--[^\n]*~si',
 79:                     array($this, 'callback')
 80:                 );
 81:             }
 82:         }
 83: 
 84:         return array(implode(' ', $res), $this->remaining);
 85:     }
 86: 
 87: 
 88:     /** @internal */
 89:     public function callback($m)
 90:     {
 91:         $m = $m[0];
 92:         if ($m[0] === "'" || $m[0] === '"' || $m[0] === '/' || $m[0] === '-') { // string or comment
 93:             return $m;
 94: 
 95:         } elseif ($m === '?') { // placeholder
 96:             if ($this->counter >= count($this->params)) {
 97:                 throw new Nette\InvalidArgumentException('There are more placeholders than passed parameters.');
 98:             }
 99:             return $this->formatValue($this->params[$this->counter++]);
100: 
101:         } else { // command
102:             $this->arrayMode = $this->arrayModes[strtoupper($m)];
103:             return $m;
104:         }
105:     }
106: 
107: 
108:     private function formatValue($value)
109:     {
110:         if (is_string($value)) {
111:             if (strlen($value) > 20) {
112:                 $this->remaining[] = $value;
113:                 return '?';
114: 
115:             } else {
116:                 return $this->connection->quote($value);
117:             }
118: 
119:         } elseif (is_int($value)) {
120:             return (string) $value;
121: 
122:         } elseif (is_float($value)) {
123:             return rtrim(rtrim(number_format($value, 10, '.', ''), '0'), '.');
124: 
125:         } elseif (is_bool($value)) {
126:             return $this->driver->formatBool($value);
127: 
128:         } elseif ($value === NULL) {
129:             return 'NULL';
130: 
131:         } elseif ($value instanceof Table\IRow) {
132:             return $value->getPrimary();
133: 
134:         } elseif (is_array($value) || $value instanceof \Traversable) {
135:             $vx = $kx = array();
136: 
137:             if ($value instanceof \Traversable) {
138:                 $value = iterator_to_array($value);
139:             }
140: 
141:             if (array_key_exists(0, $value)) { // non-associative; value, value, value
142:                 foreach ($value as $v) {
143:                     if (is_array($v) && isset($v[0])) { // no-associative; (value), (value), (value)
144:                         $vx[] = '(' . $this->formatValue($v) . ')';
145:                     } else {
146:                         $vx[] = $this->formatValue($v);
147:                     }
148:                 }
149:                 if ($this->arrayMode === 'union') {
150:                     return implode(' ', $vx);
151:                 }
152:                 return implode(', ', $vx);
153: 
154:             } elseif ($this->arrayMode === 'values') { // (key, key, ...) VALUES (value, value, ...)
155:                 $this->arrayMode = 'multi';
156:                 foreach ($value as $k => $v) {
157:                     $kx[] = $this->driver->delimite($k);
158:                     $vx[] = $this->formatValue($v);
159:                 }
160:                 return '(' . implode(', ', $kx) . ') VALUES (' . implode(', ', $vx) . ')';
161: 
162:             } elseif ($this->arrayMode === 'select') { // (key, key, ...) SELECT value, value, ...
163:                 $this->arrayMode = 'union';
164:                 foreach ($value as $k => $v) {
165:                     $kx[] = $this->driver->delimite($k);
166:                     $vx[] = $this->formatValue($v);
167:                 }
168:                 return '(' . implode(', ', $kx) . ') SELECT ' . implode(', ', $vx);
169: 
170:             } elseif ($this->arrayMode === 'assoc') { // key=value, key=value, ...
171:                 foreach ($value as $k => $v) {
172:                     if (substr($k, -1) === '=') {
173:                         $k2 = $this->driver->delimite(substr($k, 0, -2));
174:                         $vx[] = $k2 . '=' . $k2 . ' ' . substr($k, -2, 1) . ' ' . $this->formatValue($v);
175:                     } else {
176:                         $vx[] = $this->driver->delimite($k) . '=' . $this->formatValue($v);
177:                     }
178:                 }
179:                 return implode(', ', $vx);
180: 
181:             } elseif ($this->arrayMode === 'multi') { // multiple insert (value, value, ...), ...
182:                 foreach ($value as $v) {
183:                     $vx[] = $this->formatValue($v);
184:                 }
185:                 return '(' . implode(', ', $vx) . ')';
186: 
187:             } elseif ($this->arrayMode === 'union') { // UNION ALL SELECT value, value, ...
188:                 foreach ($value as $v) {
189:                     $vx[] = $this->formatValue($v);
190:                 }
191:                 return 'UNION ALL SELECT ' . implode(', ', $vx);
192: 
193:             } elseif ($this->arrayMode === 'and') { // (key [operator] value) AND ...
194:                 foreach ($value as $k => $v) {
195:                     $k = $this->driver->delimite($k);
196:                     if (is_array($v)) {
197:                         $vx[] = $v ? ($k . ' IN (' . $this->formatValue(array_values($v)) . ')') : '1=0';
198:                     } else {
199:                         $v = $this->formatValue($v);
200:                         $vx[] = $k . ($v === 'NULL' ? ' IS ' : ' = ') . $v;
201:                     }
202:                 }
203:                 return $value ? '(' . implode(') AND (', $vx) . ')' : '1=1';
204: 
205:             } elseif ($this->arrayMode === 'order') { // key, key DESC, ...
206:                 foreach ($value as $k => $v) {
207:                     $vx[] = $this->driver->delimite($k) . ($v > 0 ? '' : ' DESC');
208:                 }
209:                 return implode(', ', $vx);
210:             }
211: 
212:         } elseif ($value instanceof \DateTime || $value instanceof \DateTimeInterface) {
213:             return $this->driver->formatDateTime($value);
214: 
215:         } elseif ($value instanceof SqlLiteral) {
216:             $this->remaining = array_merge($this->remaining, $value->getParameters());
217:             return $value->__toString();
218: 
219:         } else {
220:             $this->remaining[] = $value;
221:             return '?';
222:         }
223:     }
224: 
225: }
226: