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