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