1: <?php
  2: /**
  3:  * This file contains various helper functions to prepare and handle SQL data.
  4:  *
  5:  * @package    Setup
  6:  * @subpackage Helper_MySQL
  7:  * @author     Unknown
  8:  * @copyright  four for business AG <www.4fb.de>
  9:  * @license    http://www.contenido.org/license/LIZENZ.txt
 10:  * @link       http://www.4fb.de
 11:  * @link       http://www.contenido.org
 12:  */
 13: 
 14: defined('CON_FRAMEWORK') || die('Illegal call: Missing framework initialization - request aborted.');
 15: 
 16: /**
 17:  * Executes a file of SQL queries
 18:  * 
 19:  * @param       $db
 20:  * @param       $prefix
 21:  * @param       $file
 22:  * @param array $replacements
 23:  *
 24:  * @return bool
 25:  * @throws cInvalidArgumentException
 26:  */
 27: function injectSQL($db, $prefix, $file, $replacements = array()) {
 28:     $file = trim($file);
 29: 
 30:     if (!is_readable($file)) {
 31:         return false;
 32:     }
 33: 
 34:     $sqlFile = cFileHandler::read($file);
 35: 
 36:     $sqlFile = removeComments($sqlFile);
 37:     $sqlFile = removeRemarks($sqlFile);
 38:     $sqlFile = str_replace("!PREFIX!", $prefix, $sqlFile);
 39:     $sqlFile = trim($sqlFile);
 40: 
 41:     $sqlChunks = splitSqlFile(trim($sqlFile), ";");
 42: 
 43:     foreach ($sqlChunks as $sqlChunk) {
 44:         foreach ($replacements as $find => $replace) {
 45:             $sqlChunk = str_replace($find, $replace, $sqlChunk);
 46:         }
 47: 
 48:         $db->query($sqlChunk);
 49: 
 50:         if ($db->getErrorNumber() != 0) {
 51:             logSetupFailure("Unable to execute SQL statement:\n" . $sqlChunk . "\nMysql Error: " . $db->getErrorMessage() . " (" . $db->getErrorNumber() . ")");
 52:             $_SESSION['install_failedchunks'] = true;
 53:         }
 54:     }
 55: 
 56:     return true;
 57: }
 58: 
 59: /**
 60:  * Adds the autoincrement property to all primary keys in CONTENIDO tables
 61:  * @param cDB $db
 62:  * @param array $cfg
 63:  */
 64: function addAutoIncrementToTables($db, $cfg) {
 65:     // All primary keys in tables except these below!
 66:     $filterTables = array(
 67:         $cfg['sql']['sqlprefix'] . '_groups',
 68:         $cfg['sql']['sqlprefix'] . '_pica_alloc_con',
 69:         $cfg['sql']['sqlprefix'] . '_pica_lang',
 70:         $cfg['sql']['sqlprefix'] . '_sequence',
 71:         $cfg['sql']['sqlprefix'] . '_phplib_active_sessions',
 72:         $cfg['sql']['sqlprefix'] . '_online_user',
 73:         $cfg['sql']['sqlprefix'] . '_pi_linkwhitelist',
 74:         $cfg['sql']['sqlprefix'] . '_phplib_auth_user_md5',
 75:         $cfg['sql']['sqlprefix'] . '_user',
 76:         $cfg['sql']['sqlprefix'] . '_iso_639_2',
 77:         $cfg['sql']['sqlprefix'] . '_iso_3166'
 78:     );
 79: 
 80:     $sql = $db->prepare('SHOW TABLES FROM `%s`', $cfg['db']['connection']['database']);
 81:     $db->query($sql);
 82: 
 83:     if ($db->getErrorNumber() != 0) {
 84:         logSetupFailure("Unable to execute SQL statement:\n" . $sql . "\nMysql Error: " . $db->getErrorMessage() . " (" . $db->getErrorNumber() . ")");
 85:         $_SESSION['install_failedupgradetable'] = true;
 86:     }
 87: 
 88:     $aRows = array();
 89:     while ($db->nextRecord()) {
 90:         $aRows[] = $db->getRecord();
 91:     }
 92:     foreach ($aRows as $row) {
 93:         if (in_array($row[0], $filterTables) === false && strpos($row[0], $cfg['sql']['sqlprefix'] . '_') !== false) {
 94:             alterTableHandling($row[0]);
 95:         }
 96:     }
 97: 
 98:     // Security reason: Check iterator alter table before drop table. The count of Tables must be not less than 65.
 99:     if (count($aRows) > 65) {
100:         $sql = 'DROP TABLE IF EXISTS ' . $cfg['sql']['sqlprefix'] . '_sequence';
101:         $db->query($sql);
102:     }
103: }
104: 
105: /**
106:  * Adds salts to the passwords of the backend and frontend users. Converts old passwords into new ones
107:  * @param object $db The database object
108:  */
109: function addSalts($db) {
110:     global $cfg;
111: 
112:     $db2 = getSetupMySQLDBConnection();
113: 
114:     $sql = "SHOW COLUMNS FROM %s LIKE 'salt'";
115:     $sql = sprintf($sql, $cfg['tab']['user']);
116: 
117:     $db->query($sql);
118:     if ($db->numRows() == 0) {
119:         $db2->query("ALTER TABLE ".$cfg["tab"]["user"]." CHANGE password password VARCHAR(64)");
120:         $db2->query("ALTER TABLE ".$cfg["tab"]["user"]." ADD salt VARCHAR(32) AFTER password");
121:     }
122: 
123:     $db->query("SELECT * FROM ".$cfg["tab"]["user"]);
124:     while ($db->nextRecord()) {
125:         if ($db->f("salt") == "") {
126:             $salt = md5($db->f("username").rand(1000, 9999).rand(1000, 9999).rand(1000, 9999));
127:             $db2->query("UPDATE ".$cfg["tab"]["user"]." SET salt='".$salt."' WHERE user_id='".$db->f("user_id")."'");
128:             $db2->query("UPDATE ".$cfg["tab"]["user"]." SET password='".hash("sha256", $db->f("password").$salt)."' WHERE user_id='".$db->f("user_id")."'");
129:         }
130:     }
131: 
132:     $sql = "SHOW COLUMNS FROM %s LIKE 'salt'";
133:     $sql = sprintf($sql, $cfg['tab']['frontendusers']);
134: 
135:     $db->query($sql);
136:     if ($db->numRows() == 0) {
137:         $db2->query("ALTER TABLE ".$cfg["tab"]["frontendusers"]." CHANGE password password VARCHAR(64)");
138:         $db2->query("ALTER TABLE ".$cfg["tab"]["frontendusers"]." ADD salt VARCHAR(32) AFTER password");
139:     }
140: 
141:     $db->query("SELECT * FROM ".$cfg["tab"]["frontendusers"]);
142:     while ($db->nextRecord()) {
143:         if ($db->f("salt") == "") {
144:             $salt = md5($db->f("username").rand(1000, 9999).rand(1000, 9999).rand(1000, 9999));
145:             $db2->query("UPDATE ".$cfg["tab"]["frontendusers"]." SET salt='".$salt."' WHERE idfrontenduser='".$db->f("idfrontenduser")."'");
146:             $db2->query("UPDATE ".$cfg["tab"]["frontendusers"]." SET password='".hash("sha256", $db->f("password").$salt)."' WHERE idfrontenduser='".$db->f("idfrontenduser")."'");
147:         }
148:     }
149: }
150: 
151: function urlDecodeTables($db) {
152:     global $cfg;
153: 
154:     urlDecodeTable($db, $cfg['tab']['frontendusers']);
155:     urlDecodeTable($db, $cfg['tab']['content']);
156:     urlDecodeTable($db, $cfg['tab']['properties']);
157:     urlDecodeTable($db, $cfg['tab']['upl_meta']);
158:     urlDecodeTable($db, $cfg['tab']['container']);
159:     urlDecodeTable($db, $cfg['sql']['sqlprefix'] . '_pica_lang', true);
160:     urlDecodeTable($db, $cfg['sql']['sqlprefix'] . '_pi_news_rcp', true);
161:     urlDecodeTable($db, $cfg['tab']['art_lang']);
162:     urlDecodeTable($db, $cfg['tab']['user_prop']);
163:     urlDecodeTable($db, $cfg['tab']['system_prop']);
164:     urlDecodeTable($db, $cfg['tab']['art_spec']);
165:     urlDecodeTable($db, $cfg['sql']['sqlprefix'] . '_pi_news_jobs', true);
166: 
167: }
168: 
169: function urlDecodeTable($db, $table, $checkTableExists = false) {
170:     if ($checkTableExists === true) {
171:         $db->query('SHOW TABLES LIKE "%s"', $table);
172:         if ($db->nextRecord() === false) {
173:             return;
174:         }
175:     }
176: 
177:     $sql = "SELECT * FROM " . $table;
178:     $db->query($sql);
179: 
180:     $db2 = getSetupMySQLDBConnection(false);
181: 
182:     while ($db->nextRecord()) {
183: 
184:         $row = $db->toArray(cDb::FETCH_ASSOC);
185: 
186:         $sql = "UPDATE " . $table . " SET ";
187:         foreach ($row as $key => $value) {
188:             if (strlen($value) > 0) {
189:                 $sql .= "`" . $key . "`='" . $db->escape(urldecode($value)) . "', ";
190:             }
191:         }
192:         $sql = substr($sql, 0, strlen($sql) - 2) . " WHERE ";
193:         foreach ($row as $key => $value) {
194:             if (strlen($value) > 0) {
195:                 $sql .= "`" . $key . "`= '" . $db->escape($value) . "' AND ";
196:             }
197:         }
198:         $sql = substr($sql, 0, strlen($sql) - 5) . ";";
199: 
200:         $db2->query($sql);
201:     }
202: }
203: 
204: function convertToDatetime($db, $cfg) {
205:     $db->query('SHOW TABLES LIKE "%s"', $cfg["sql"]["sqlprefix"] . "_piwf_art_allocation");
206:     if ($db->nextRecord()) {
207:         $db->query("ALTER TABLE " . $cfg['sql']['sqlprefix'] . "_piwf_art_allocation CHANGE `starttime` `starttime` DATETIME NOT NULL");
208:     }
209: 
210:     $db->query("ALTER TABLE " . $cfg['sql']['sqlprefix'] . "_template_conf CHANGE `created` `created` DATETIME NOT NULL");
211: }
212: 
213: /**
214:  * Changes the primary key of the given table to an auto increment type
215:  * @param string $tableName
216:  */
217: function alterTableHandling($tableName) {
218:     $db = getSetupMySQLDBConnection(false);
219:     $dbAlter = getSetupMySQLDBConnection(false);
220: 
221:     $sql = $db->prepare('SHOW KEYS FROM `%s` WHERE Key_name="PRIMARY"', $tableName);
222:     $db->query($sql);
223:     while ($db->nextRecord()) {
224:         $row = $db->getRecord();
225:         $primaryKey = $row[4];
226:         $sqlAlter = $dbAlter->prepare('ALTER TABLE `%s` CHANGE `%s` `%s` INT(11) NOT NULL AUTO_INCREMENT', $tableName, $primaryKey, $primaryKey);
227:         $dbAlter->query($sqlAlter);
228:         if ($dbAlter->getErrorNumber() != 0) {
229:             logSetupFailure("Unable to execute SQL statement:\n" . $sqlAlter . "\nMysql Error: " . $dbAlter->getErrorMessage() . " (" . $dbAlter->getErrorNumber() . ")");
230:             $_SESSION['install_failedupgradetable'] = true;
231:         }
232:     }
233: }
234: 
235: /**
236:  * Will strip the sql comment lines out of an uploaded sql file
237:  * specifically for mssql and postgres type files in the install....
238:  * @param   string  $output
239:  * @return  string
240:  */
241: function removeComments(&$output) {
242:     $lines = explode("\n", $output);
243:     $output = "";
244: 
245:     // try to keep mem. use down
246:     $linecount = count($lines);
247: 
248:     $in_comment = false;
249:     for ($i = 0; $i < $linecount; $i++) {
250:         if (preg_match("/^\/\*/", preg_quote($lines[$i]))) {
251:             $in_comment = true;
252:         }
253: 
254:         if (!$in_comment) {
255:             $output .= $lines[$i] . "\n";
256:         }
257: 
258:         if (preg_match("/\*\/$/", preg_quote($lines[$i]))) {
259:             $in_comment = false;
260:         }
261:     }
262: 
263:     unset($lines);
264:     return $output;
265: }
266: 
267: /**
268:  * Will strip the sql comment lines out of an uploaded sql file
269:  * @param   string  $sql
270:  * @return  array
271:  */
272: function removeRemarks($sql) {
273:     $lines = explode("\n", $sql);
274: 
275:     // try to keep mem. use down
276:     $sql = "";
277: 
278:     $linecount = count($lines);
279:     $output = "";
280: 
281:     for ($i = 0; $i < $linecount; $i++) {
282:         if (($i != ($linecount - 1)) || (strlen($lines[$i]) > 0)) {
283:             if (!empty($lines[$i]) && $lines[$i][0] != "#") {
284:                 $output .= $lines[$i] . "\n";
285:             } else {
286:                 $output .= "\n";
287:             }
288:             // Trading a bit of speed for lower mem. use here.
289:             $lines[$i] = "";
290:         }
291:     }
292: 
293:     return $output;
294: }
295: 
296: /**
297:  * Will split an uploaded sql file into single sql statements.
298:  * Note: expects trim() to have already been run on $sql.
299:  * @param   string  $sql
300:  * @param   string  $delimiter
301:  * @return  array
302:  */
303: function splitSqlFile($sql, $delimiter) {
304:     // Split up our string into "possible" SQL statements.
305:     $tokens = explode($delimiter, $sql);
306: 
307:     // try to save mem.
308:     $sql = "";
309:     $output = array();
310: 
311:     // we don't actually care about the matches preg gives us.
312:     $matches = array();
313: 
314:     // this is faster than calling count($oktens) every time thru the loop.
315:     $token_count = count($tokens);
316:     for ($i = 0; $i < $token_count; $i++) {
317:         // Don't wanna add an empty string as the last thing in the array.
318:         if (($i != ($token_count - 1)) || (strlen($tokens[$i] > 0))) {
319:             // This is the total number of single quotes in the token.
320:             $total_quotes = preg_match_all("/'/", $tokens[$i], $matches);
321:             // Counts single quotes that are preceded by an odd number of backslashes,
322:             // which means they're escaped quotes.
323:             $escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$i], $matches);
324: 
325:             $unescaped_quotes = $total_quotes - $escaped_quotes;
326: 
327:             // If the number of unescaped quotes is even, then the delimiter did NOT occur inside a string literal.
328:             if (($unescaped_quotes % 2) == 0) {
329:                 // It's a complete sql statement.
330:                 $output[] = $tokens[$i];
331:                 // save memory.
332:                 $tokens[$i] = "";
333:             } else {
334:                 // incomplete sql statement. keep adding tokens until we have a complete one.
335:                 // $temp will hold what we have so far.
336:                 $temp = $tokens[$i] . $delimiter;
337:                 // save memory..
338:                 $tokens[$i] = "";
339: 
340:                 // Do we have a complete statement yet?
341:                 $complete_stmt = false;
342: 
343:                 for ($j = $i + 1; (!$complete_stmt && ($j < $token_count)); $j++) {
344:                     // This is the total number of single quotes in the token.
345:                     $total_quotes = preg_match_all("/'/", $tokens[$j], $matches);
346:                     // Counts single quotes that are preceded by an odd number of backslashes,
347:                     // which means they're escaped quotes.
348:                     $escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$j], $matches);
349: 
350:                     $unescaped_quotes = $total_quotes - $escaped_quotes;
351: 
352:                     if (($unescaped_quotes % 2) == 1) {
353:                         // odd number of unescaped quotes. In combination with the previous incomplete
354:                         // statement(s), we now have a complete statement. (2 odds always make an even)
355:                         $output[] = $temp . $tokens[$j];
356: 
357:                         // save memory.
358:                         $tokens[$j] = "";
359:                         $temp = "";
360: 
361:                         // exit the loop.
362:                         $complete_stmt = true;
363:                         // make sure the outer loop continues at the right point.
364:                         $i = $j;
365:                     } else {
366:                         // even number of unescaped quotes. We still don't have a complete statement.
367:                         // (1 odd and 1 even always make an odd)
368:                         $temp .= $tokens[$j] . $delimiter;
369:                         // save memory.
370:                         $tokens[$j] = "";
371:                     }
372:                 } // for..
373:             } // else
374:         }
375:     }
376: 
377:     return $output;
378: }
379: