libraries/import/sql.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * SQL import plugin for phpMyAdmin
   5  *
   6  * @version $Id$
   7  * @package phpMyAdmin-Import
   8  */
   9 if (! defined('PHPMYADMIN')) {
  10     exit;
  11 }
  12
  13 /**
  14  *
  15  */
  16 if (isset($plugin_list)) {
  17     $plugin_list['sql'] = array(
  18         'text'          => 'strSQL',
  19         'extension'     => 'sql',
  20         'options_text'  => 'strOptions',
  21     );
  22     $compats = PMA_DBI_getCompatibilities();
  23     if (count($compats) > 0) {
  24         $values = array();
  25         foreach($compats as $val) {
  26             $values[$val] = $val;
  27         }
  28         $plugin_list['sql']['options'] = array(
  29             array(
  30                 'type'      => 'select',
  31                 'name'      => 'compatibility',
  32                 'text'      => 'strSQLCompatibility',
  33                 'values'    => $values,
  34                 'doc'       => array(
  35                     'manual_MySQL_Database_Administration',
  36                     'Server_SQL_mode',
  37                 ),
  38             ),
  39             array(
  40                 'type' => 'bool',
  41                 'name' => 'no_auto_value_on_zero',
  42                 'text' => 'strDoNotAutoIncrementZeroValues',
  43                 'doc'       => array(
  44                     'manual_MySQL_Database_Administration',
  45                     'Server_SQL_mode',
  46                     'sqlmode_no_auto_value_on_zero'
  47                 ),
  48
  49             ),
  50         );
  51     }
  52
  53     /* We do not define function when plugin is just queried for information above */
  54     return;
  55 }
  56
  57 $buffer = '';
  58 // Defaults for parser
  59 $sql = '';
  60 $start_pos = 0;
  61 $i = 0;
  62 $len= 0;
  63 $big_value = 2147483647;
  64 $delimiter_keyword = 'DELIMITER '; // include the space because it's mandatory
  65 $length_of_delimiter_keyword = strlen($delimiter_keyword);
  66
  67 if (isset($_POST['sql_delimiter'])) {
  68     $sql_delimiter = $_POST['sql_delimiter'];
  69 } else {
  70     $sql_delimiter = ';';
  71 }
  72
  73 // Handle compatibility options
  74 $sql_modes = array();
  75 if (isset($_REQUEST['sql_compatibility']) && 'NONE' != $_REQUEST['sql_compatibility']) {
  76     $sql_modes[] = $_REQUEST['sql_compatibility'];
  77 }
  78 if (isset($_REQUEST['sql_no_auto_value_on_zero'])) {
  79     $sql_modes[] = 'NO_AUTO_VALUE_ON_ZERO';
  80 }
  81 if (count($sql_modes) > 0) {
  82     PMA_DBI_try_query('SET SQL_MODE="' . implode(',', $sql_modes) . '"');
  83 }
  84 unset($sql_modes);
  85
  86 /**
  87  * will be set in PMA_importGetNextChunk()
  88  *
  89  * @global boolean $GLOBALS['finished']
  90  */
  91 $GLOBALS['finished'] = false;
  92
  93 while (!($GLOBALS['finished'] && $i >= $len) && !$error && !$timeout_passed) {
  94     $data = PMA_importGetNextChunk();
  95     if ($data === FALSE) {
  96         // subtract data we didn't handle yet and stop processing
  97         $offset -= strlen($buffer);
  98         break;
  99     } elseif ($data === TRUE) {
 100         // Handle rest of buffer
 101     } else {
 102         // Append new data to buffer
 103         $buffer .= $data;
 104         // free memory
 105         unset($data);
 106         // Do not parse string when we're not at the end and don't have ; inside
 107         if ((strpos($buffer, $sql_delimiter, $i) === FALSE) && !$GLOBALS['finished'])  {
 108             continue;
 109         }
 110     }
 111     // Current length of our buffer
 112     $len = strlen($buffer);
 113
 114     // Grab some SQL queries out of it
 115     while ($i < $len) {
 116         $found_delimiter = false;
 117         // Find first interesting character
 118         $old_i = $i;
 119         // this is about 7 times faster that looking for each sequence i
 120         // one by one with strpos()
 121         if (preg_match('/(\'|"|#|-- |\/\*|`|(?i)(?<![A-Z0-9_])' . $delimiter_keyword . ')/', $buffer, $matches, PREG_OFFSET_CAPTURE, $i)) {
 122             // in $matches, index 0 contains the match for the complete
 123             // expression but we don't use it
 124             $first_position = $matches[1][1];
 125         } else {
 126             $first_position = $big_value;
 127         }
 128         /**
 129          * @todo we should not look for a delimiter that might be
 130          *       inside quotes (or even double-quotes)
 131          */
 132         // the cost of doing this one with preg_match() would be too high
 133         $first_sql_delimiter = strpos($buffer, $sql_delimiter, $i);
 134         if ($first_sql_delimiter === FALSE) {
 135             $first_sql_delimiter = $big_value;
 136         } else {
 137             $found_delimiter = true;
 138         }
 139
 140         // set $i to the position of the first quote, comment.start or delimiter found
 141         $i = min($first_position, $first_sql_delimiter);
 142
 143         if ($i == $big_value) {
 144             // none of the above was found in the string
 145
 146             $i = $old_i;
 147             if (!$GLOBALS['finished']) {
 148                 break;
 149             }
 150             // at the end there might be some whitespace...
 151             if (trim($buffer) == '') {
 152                 $buffer = '';
 153                 $len = 0;
 154                 break;
 155             }
 156             // We hit end of query, go there!
 157             $i = strlen($buffer) - 1;
 158         }
 159
 160         // Grab current character
 161         $ch = $buffer[$i];
 162
 163         // Quotes
 164         if (strpos('\'"`', $ch) !== FALSE) {
 165             $quote = $ch;
 166             $endq = FALSE;
 167             while (!$endq) {
 168                 // Find next quote
 169                 $pos = strpos($buffer, $quote, $i + 1);
 170                 // No quote? Too short string
 171                 if ($pos === FALSE) {
 172                     // We hit end of string => unclosed quote, but we handle it as end of query
 173                     if ($GLOBALS['finished']) {
 174                         $endq = TRUE;
 175                         $i = $len - 1;
 176                     }
 177                     $found_delimiter = false;
 178                     break;
 179                 }
 180                 // Was not the quote escaped?
 181                 $j = $pos - 1;
 182                 while ($buffer[$j] == '\\') $j--;
 183                 // Even count means it was not escaped
 184                 $endq = (((($pos - 1) - $j) % 2) == 0);
 185                 // Skip the string
 186                 $i = $pos;
 187
 188                 if ($first_sql_delimiter < $pos) {
 189                     $found_delimiter = false;
 190                 }
 191             }
 192             if (!$endq) {
 193                 break;
 194             }
 195             $i++;
 196             // Aren't we at the end?
 197             if ($GLOBALS['finished'] && $i == $len) {
 198                 $i--;
 199             } else {
 200                 continue;
 201             }
 202         }
 203
 204         // Not enough data to decide
 205         if ((($i == ($len - 1) && ($ch == '-' || $ch == '/'))
 206           || ($i == ($len - 2) && (($ch == '-' && $buffer[$i + 1] == '-')
 207             || ($ch == '/' && $buffer[$i + 1] == '*')))) && !$GLOBALS['finished']) {
 208             break;
 209         }
 210
 211         // Comments
 212         if ($ch == '#'
 213          || ($i < ($len - 1) && $ch == '-' && $buffer[$i + 1] == '-'
 214           && (($i < ($len - 2) && $buffer[$i + 2] <= ' ')
 215            || ($i == ($len - 1)  && $GLOBALS['finished'])))
 216          || ($i < ($len - 1) && $ch == '/' && $buffer[$i + 1] == '*')
 217                 ) {
 218             // Copy current string to SQL
 219             if ($start_pos != $i) {
 220                 $sql .= substr($buffer, $start_pos, $i - $start_pos);
 221             }
 222             // Skip the rest
 223             $start_of_comment = $i;
 224             // do not use PHP_EOL here instead of "\n", because the export
 225             // file might have been produced on a different system
 226             $i = strpos($buffer, $ch == '/' ? '*/' : "\n", $i);
 227             // didn't we hit end of string?
 228             if ($i === FALSE) {
 229                 if ($GLOBALS['finished']) {
 230                     $i = $len - 1;
 231                 } else {
 232                     break;
 233                 }
 234             }
 235             // Skip *
 236             if ($ch == '/') {
 237                 $i++;
 238             }
 239             // Skip last char
 240             $i++;
 241             // We need to send the comment part in case we are defining
 242             // a procedure or function and comments in it are valuable
 243             $sql .= substr($buffer, $start_of_comment, $i - $start_of_comment);
 244             // Next query part will start here
 245             $start_pos = $i;
 246             // Aren't we at the end?
 247             if ($i == $len) {
 248                 $i--;
 249             } else {
 250                 continue;
 251             }
 252         }
 253         // Change delimiter, if redefined, and skip it (don't send to server!)
 254         if (strtoupper(substr($buffer, $i, $length_of_delimiter_keyword)) == $delimiter_keyword
 255          && ($i + $length_of_delimiter_keyword < $len)) {
 256              // look for EOL on the character immediately after 'DELIMITER '
 257              // (see previous comment about PHP_EOL)
 258            $new_line_pos = strpos($buffer, "\n", $i + $length_of_delimiter_keyword);
 259            // it might happen that there is no EOL
 260            if (FALSE === $new_line_pos) {
 261                $new_line_pos = $len;
 262            }
 263            $sql_delimiter = substr($buffer, $i + $length_of_delimiter_keyword, $new_line_pos - $i - $length_of_delimiter_keyword);
 264            $i = $new_line_pos + 1;
 265            // Next query part will start here
 266            $start_pos = $i;
 267            continue;
 268         }
 269
 270         // End of SQL
 271         if ($found_delimiter || ($GLOBALS['finished'] && ($i == $len - 1))) {
 272             $tmp_sql = $sql;
 273             if ($start_pos < $len) {
 274                 $length_to_grab = $i - $start_pos;
 275
 276                 if (! $found_delimiter) {
 277                     $length_to_grab++;
 278                 }
 279                 $tmp_sql .= substr($buffer, $start_pos, $length_to_grab);
 280                 unset($length_to_grab);
 281             }
 282             // Do not try to execute empty SQL
 283             if (! preg_match('/^([\s]*;)*$/', trim($tmp_sql))) {
 284                 $sql = $tmp_sql;
 285                 PMA_importRunQuery($sql, substr($buffer, 0, $i + strlen($sql_delimiter)));
 286                 $buffer = substr($buffer, $i + strlen($sql_delimiter));
 287                 // Reset parser:
 288                 $len = strlen($buffer);
 289                 $sql = '';
 290                 $i = 0;
 291                 $start_pos = 0;
 292                 // Any chance we will get a complete query?
 293                 //if ((strpos($buffer, ';') === FALSE) && !$GLOBALS['finished']) {
 294                 if ((strpos($buffer, $sql_delimiter) === FALSE) && !$GLOBALS['finished']) {
 295                     break;
 296                 }
 297             } else {
 298                 $i++;
 299                 $start_pos = $i;
 300             }
 301         }
 302     } // End of parser loop
 303 } // End of import loop
 304 // Commit any possible data in buffers
 305 PMA_importRunQuery('', substr($buffer, 0, $len));
 306 PMA_importRunQuery();
 307 ?>