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: 
177:         $row = $db->toArray(FETCH_ASSOC);
178: 
179:         $sql = "UPDATE " . $table . " SET ";
180:         foreach ($row as $key => $value) {
181:             if (strlen($value) > 0) {
182:                 $sql .= "`" . $key . "`='" . cSecurity::escapeDB(urldecode($value), $db) . "', ";
183:             }
184:         }
185:         $sql = substr($sql, 0, strlen($sql) - 2) . " WHERE ";
186:         foreach ($row as $key => $value) {
187:             if (strlen($value) > 0) {
188:                 $sql .= "`" . $key . "`= '" . cSecurity::escapeDB($value, $db) . "' AND ";
189:             }
190:         }
191:         $sql = substr($sql, 0, strlen($sql) - 5) . ";";
192: 
193:         $db2->query($sql);
194:     }
195: }
196: 
197: function convertToDatetime($db, $cfg) {
198:     $db->query('SHOW TABLES LIKE "%s"', $cfg["sql"]["sqlprefix"] . "_piwf_art_allocation");
199:     if ($db->nextRecord()) {
200:         $db->query("ALTER TABLE " . $cfg['sql']['sqlprefix'] . "_piwf_art_allocation CHANGE `starttime` `starttime` DATETIME NOT NULL");
201:     }
202: 
203:     $db->query("ALTER TABLE " . $cfg['sql']['sqlprefix'] . "_template_conf CHANGE `created` `created` DATETIME NOT NULL");
204: }
205: 
206: /**
207:  * Changes the primary key of the given table to an auto increment type
208:  * @param string $tableName
209:  */
210: function alterTableHandling($tableName) {
211:     $db = getSetupMySQLDBConnection(false);
212:     $dbAlter = getSetupMySQLDBConnection(false);
213: 
214:     $sql = $db->prepare('SHOW KEYS FROM `%s` WHERE Key_name="PRIMARY"', $tableName);
215:     $db->query($sql);
216:     while ($db->nextRecord()) {
217:         $row = $db->getRecord();
218:         $primaryKey = $row[4];
219:         $sqlAlter = $dbAlter->prepare('ALTER TABLE `%s` CHANGE `%s` `%s` INT(11) NOT NULL AUTO_INCREMENT', $tableName, $primaryKey, $primaryKey);
220:         $dbAlter->query($sqlAlter);
221:         if ($dbAlter->getErrorNumber() != 0) {
222:             logSetupFailure("Unable to execute SQL statement:\n" . $sqlAlter . "\nMysql Error: " . $dbAlter->getErrorMessage() . " (" . $dbAlter->getErrorNumber() . ")");
223:             $_SESSION['install_failedupgradetable'] = true;
224:         }
225:     }
226: }
227: 
228: /**
229:  * Will strip the sql comment lines out of an uploaded sql file
230:  * specifically for mssql and postgres type files in the install....
231:  * @param   string  $output
232:  * @return  string
233:  */
234: function removeComments(&$output) {
235:     $lines = explode("\n", $output);
236:     $output = "";
237: 
238:     // try to keep mem. use down
239:     $linecount = count($lines);
240: 
241:     $in_comment = false;
242:     for ($i = 0; $i < $linecount; $i++) {
243:         if (preg_match("/^\/\*/", preg_quote($lines[$i]))) {
244:             $in_comment = true;
245:         }
246: 
247:         if (!$in_comment) {
248:             $output .= $lines[$i] . "\n";
249:         }
250: 
251:         if (preg_match("/\*\/$/", preg_quote($lines[$i]))) {
252:             $in_comment = false;
253:         }
254:     }
255: 
256:     unset($lines);
257:     return $output;
258: }
259: 
260: /**
261:  * Will strip the sql comment lines out of an uploaded sql file
262:  * @param   string  $sql
263:  * @return  string
264:  */
265: function removeRemarks($sql) {
266:     $lines = explode("\n", $sql);
267: 
268:     // try to keep mem. use down
269:     $sql = "";
270: 
271:     $linecount = count($lines);
272:     $output = "";
273: 
274:     for ($i = 0; $i < $linecount; $i++) {
275:         if (($i != ($linecount - 1)) || (strlen($lines[$i]) > 0)) {
276:             if (!empty($lines[$i]) && $lines[$i][0] != "#") {
277:                 $output .= $lines[$i] . "\n";
278:             } else {
279:                 $output .= "\n";
280:             }
281:             // Trading a bit of speed for lower mem. use here.
282:             $lines[$i] = "";
283:         }
284:     }
285: 
286:     return $output;
287: }
288: 
289: /**
290:  * Will split an uploaded sql file into single sql statements.
291:  * Note: expects trim() to have already been run on $sql.
292:  * @param   string  $sql
293:  * @param   string  $delimiter
294:  * @return  string
295:  */
296: function splitSqlFile($sql, $delimiter) {
297:     // Split up our string into "possible" SQL statements.
298:     $tokens = explode($delimiter, $sql);
299: 
300:     // try to save mem.
301:     $sql = "";
302:     $output = array();
303: 
304:     // we don't actually care about the matches preg gives us.
305:     $matches = array();
306: 
307:     // this is faster than calling count($oktens) every time thru the loop.
308:     $token_count = count($tokens);
309:     for ($i = 0; $i < $token_count; $i++) {
310:         // Don't wanna add an empty string as the last thing in the array.
311:         if (($i != ($token_count - 1)) || (strlen($tokens[$i] > 0))) {
312:             // This is the total number of single quotes in the token.
313:             $total_quotes = preg_match_all("/'/", $tokens[$i], $matches);
314:             // Counts single quotes that are preceded by an odd number of backslashes,
315:             // which means they're escaped quotes.
316:             $escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$i], $matches);
317: 
318:             $unescaped_quotes = $total_quotes - $escaped_quotes;
319: 
320:             // If the number of unescaped quotes is even, then the delimiter did NOT occur inside a string literal.
321:             if (($unescaped_quotes % 2) == 0) {
322:                 // It's a complete sql statement.
323:                 $output[] = $tokens[$i];
324:                 // save memory.
325:                 $tokens[$i] = "";
326:             } else {
327:                 // incomplete sql statement. keep adding tokens until we have a complete one.
328:                 // $temp will hold what we have so far.
329:                 $temp = $tokens[$i] . $delimiter;
330:                 // save memory..
331:                 $tokens[$i] = "";
332: 
333:                 // Do we have a complete statement yet?
334:                 $complete_stmt = false;
335: 
336:                 for ($j = $i + 1; (!$complete_stmt && ($j < $token_count)); $j++) {
337:                     // This is the total number of single quotes in the token.
338:                     $total_quotes = preg_match_all("/'/", $tokens[$j], $matches);
339:                     // Counts single quotes that are preceded by an odd number of backslashes,
340:                     // which means they're escaped quotes.
341:                     $escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$j], $matches);
342: 
343:                     $unescaped_quotes = $total_quotes - $escaped_quotes;
344: 
345:                     if (($unescaped_quotes % 2) == 1) {
346:                         // odd number of unescaped quotes. In combination with the previous incomplete
347:                         // statement(s), we now have a complete statement. (2 odds always make an even)
348:                         $output[] = $temp . $tokens[$j];
349: 
350:                         // save memory.
351:                         $tokens[$j] = "";
352:                         $temp = "";
353: 
354:                         // exit the loop.
355:                         $complete_stmt = true;
356:                         // make sure the outer loop continues at the right point.
357:                         $i = $j;
358:                     } else {
359:                         // even number of unescaped quotes. We still don't have a complete statement.
360:                         // (1 odd and 1 even always make an odd)
361:                         $temp .= $tokens[$j] . $delimiter;
362:                         // save memory.
363:                         $tokens[$j] = "";
364:                     }
365:                 } // for..
366:             } // else
367:         }
368:     }
369: 
370:     return $output;
371: }
372: