.htaccess
1 year ago
ArrayData.php
1 year ago
DBObject.php
1 year ago
Engine.php
1 year ago
Mysqldump.php
3 days ago
ReflectionObject.php
1 year ago
SleekStore.php
1 year ago
SqlStatementParser.php
3 days ago
index.html
1 year ago
web.config
1 year ago
SqlStatementParser.php
262 lines
| 1 | <?php |
| 2 | |
| 3 | namespace JetBackup\Data; |
| 4 | |
| 5 | if (!defined( '__JETBACKUP__')) die('Direct access is not allowed'); |
| 6 | |
| 7 | /** |
| 8 | * Splits a SQL dump into single statements without being fooled by quoted text. |
| 9 | * |
| 10 | * A ';' or a word like CREATE / VIEW / DEFINER= only counts when it's real SQL, not when it sits |
| 11 | * inside a quoted value. That is the whole point: text a user typed (a wp_users display_name, a |
| 12 | * profile URL) must never be read as SQL. So we keep track of whether we're inside a string, an |
| 13 | * identifier or a comment, and only react to characters that are actually SQL. |
| 14 | * |
| 15 | * It runs as a stream: give it the dump one line at a time and it hands back each finished statement |
| 16 | * when its real ';' shows up, remembering where it was between calls. That lets the importer keep |
| 17 | * reading the file line by line (low memory, resumable) like before. |
| 18 | * |
| 19 | * No database or WordPress code in here on purpose, so it can be tested on its own. |
| 20 | */ |
| 21 | class SqlStatementParser { |
| 22 | |
| 23 | // scanner modes |
| 24 | private const M_NORMAL = 0; |
| 25 | private const M_SQUOTE = 1; // inside '...' |
| 26 | private const M_DQUOTE = 2; // inside "..." |
| 27 | private const M_BACKTICK = 3; // inside `...` |
| 28 | private const M_LINE_COMMENT = 4; // -- ... or # ... (thrown away) |
| 29 | private const M_BLOCK_COMMENT = 5; // /* ... */ (thrown away) |
| 30 | private const M_COND_COMMENT = 6; // /*! ... */ (kept - MySQL runs it) |
| 31 | |
| 32 | // We only read the start of a statement to find its first word. A header is tiny; an INSERT can be huge. |
| 33 | private const HEAD_WINDOW = 1000; |
| 34 | |
| 35 | private int $mode = self::M_NORMAL; |
| 36 | private bool $escape = false; // last char inside a string was a backslash |
| 37 | private string $current = ''; // the statement we're building |
| 38 | |
| 39 | /** |
| 40 | * Feed one chunk (normally one line, newline included). Returns the statements that finished |
| 41 | * (reached their real ';') in this chunk - usually none or one. |
| 42 | * |
| 43 | * @return string[] |
| 44 | */ |
| 45 | public function feed(string $chunk): array { |
| 46 | $out = []; |
| 47 | $len = strlen($chunk); |
| 48 | |
| 49 | for ($i = 0; $i < $len; $i++) { |
| 50 | $ch = $chunk[$i]; |
| 51 | $next = $i + 1 < $len ? $chunk[$i + 1] : ''; |
| 52 | |
| 53 | switch ($this->mode) { |
| 54 | |
| 55 | case self::M_NORMAL: |
| 56 | if ($ch === "'") { $this->current .= $ch; $this->mode = self::M_SQUOTE; break; } |
| 57 | if ($ch === '"') { $this->current .= $ch; $this->mode = self::M_DQUOTE; break; } |
| 58 | if ($ch === '`') { $this->current .= $ch; $this->mode = self::M_BACKTICK; break; } |
| 59 | |
| 60 | // '#' always starts a comment; '--' only starts one when a space or line-end follows (MySQL rule) |
| 61 | if ($ch === '#') { $this->mode = self::M_LINE_COMMENT; break; } |
| 62 | if ($ch === '-' && $next === '-') { |
| 63 | $after = $i + 2 < $len ? $chunk[$i + 2] : "\n"; // end of line counts as a space |
| 64 | if ($after === ' ' || $after === "\t" || $after === "\n" || $after === "\r" || $after === "\0") { |
| 65 | $this->mode = self::M_LINE_COMMENT; |
| 66 | $i++; |
| 67 | break; |
| 68 | } |
| 69 | // not a comment (like "a--b") - treat '-' as a normal char |
| 70 | } |
| 71 | |
| 72 | // /* ... */ - keep it only if it's the /*! ... */ kind that MySQL runs |
| 73 | if ($ch === '/' && $next === '*') { |
| 74 | if (($i + 2 < $len ? $chunk[$i + 2] : '') === '!') { |
| 75 | $this->current .= '/*!'; |
| 76 | $i += 2; |
| 77 | $this->mode = self::M_COND_COMMENT; |
| 78 | } else { |
| 79 | $i++; |
| 80 | $this->mode = self::M_BLOCK_COMMENT; |
| 81 | } |
| 82 | break; |
| 83 | } |
| 84 | |
| 85 | if ($ch === ';') { // end of a statement |
| 86 | $this->current .= ';'; |
| 87 | $stmt = trim($this->current); |
| 88 | $this->current = ''; |
| 89 | if ($stmt !== '' && $stmt !== ';') $out[] = $stmt; |
| 90 | break; |
| 91 | } |
| 92 | |
| 93 | $this->current .= $ch; |
| 94 | break; |
| 95 | |
| 96 | case self::M_SQUOTE: |
| 97 | $this->current .= $ch; |
| 98 | if ($this->escape) { $this->escape = false; break; } |
| 99 | if ($ch === '\\') { $this->escape = true; break; } |
| 100 | if ($ch === "'") { |
| 101 | if ($next === "'") { $this->current .= "'"; $i++; break; } // '' is a quote inside the string, keep going |
| 102 | $this->mode = self::M_NORMAL; |
| 103 | } |
| 104 | break; |
| 105 | |
| 106 | case self::M_DQUOTE: |
| 107 | $this->current .= $ch; |
| 108 | if ($this->escape) { $this->escape = false; break; } |
| 109 | if ($ch === '\\') { $this->escape = true; break; } |
| 110 | if ($ch === '"') { |
| 111 | if ($next === '"') { $this->current .= '"'; $i++; break; } |
| 112 | $this->mode = self::M_NORMAL; |
| 113 | } |
| 114 | break; |
| 115 | |
| 116 | case self::M_BACKTICK: |
| 117 | $this->current .= $ch; |
| 118 | // no backslash escapes in identifiers; a doubled `` is an escaped backtick |
| 119 | if ($ch === '`') { |
| 120 | if ($next === '`') { $this->current .= '`'; $i++; break; } |
| 121 | $this->mode = self::M_NORMAL; |
| 122 | } |
| 123 | break; |
| 124 | |
| 125 | case self::M_LINE_COMMENT: |
| 126 | if ($ch === "\n") $this->mode = self::M_NORMAL; // ends at the newline |
| 127 | break; |
| 128 | |
| 129 | case self::M_BLOCK_COMMENT: |
| 130 | if ($ch === '*' && $next === '/') { $i++; $this->mode = self::M_NORMAL; } // ends at */ |
| 131 | break; |
| 132 | |
| 133 | case self::M_COND_COMMENT: |
| 134 | $this->current .= $ch; // keep the text; ends at */ |
| 135 | if ($ch === '*' && $next === '/') { $this->current .= '/'; $i++; $this->mode = self::M_NORMAL; } |
| 136 | break; |
| 137 | } |
| 138 | } |
| 139 | |
| 140 | return $out; |
| 141 | } |
| 142 | |
| 143 | /** True while we're in the middle of a statement (not on a clean boundary yet). */ |
| 144 | public function hasPending(): bool { |
| 145 | return $this->mode !== self::M_NORMAL || trim($this->current) !== ''; |
| 146 | } |
| 147 | |
| 148 | /** |
| 149 | * Return a last statement that had no ';' at the end (some dumps skip the final one) and reset. |
| 150 | * Returns null when there's nothing left. |
| 151 | */ |
| 152 | public function flush(): ?string { |
| 153 | $stmt = trim($this->current); |
| 154 | $this->current = ''; |
| 155 | $this->mode = self::M_NORMAL; |
| 156 | $this->escape = false; |
| 157 | return ($stmt === '' || $stmt === ';') ? null : $stmt; |
| 158 | } |
| 159 | |
| 160 | /** |
| 161 | * Split a whole SQL string in one go (tests, or callers that already hold it all in memory). |
| 162 | * |
| 163 | * @return string[] |
| 164 | */ |
| 165 | public static function split(string $sql): array { |
| 166 | $p = new self(); |
| 167 | $out = $p->feed($sql); |
| 168 | if (($tail = $p->flush()) !== null) $out[] = $tail; |
| 169 | return $out; |
| 170 | } |
| 171 | |
| 172 | /** |
| 173 | * Does this statement really start with CREATE ... VIEW? |
| 174 | * |
| 175 | * We only look at the real start - after dropping leading comments and unwrapping mysqldump's |
| 176 | * "/*!NNNNN CREATE ... VIEW ... */" wrappers - so a CREATE/VIEW that is only text inside an INSERT |
| 177 | * value never counts. |
| 178 | */ |
| 179 | public static function isCreateView(string $sql): bool { |
| 180 | $head = self::effectiveHead($sql); |
| 181 | if ($head === '' || strncasecmp($head, 'CREATE', 6) !== 0) return false; // only a CREATE can be a view |
| 182 | |
| 183 | $prefix = '/^CREATE\s+' |
| 184 | . '(?:OR\s+REPLACE\s+)?' |
| 185 | . '(?:ALGORITHM\s*=\s*\w+\s+)?' |
| 186 | . '(?:DEFINER\s*=\s*(?:`[^`]+`@`[^`]+`|\'[^\']+\'@\'[^\']+\'|"[^"]+"@"[^"]+"|\S+)\s+)?' |
| 187 | . '(?:SQL\s+SECURITY\s+(?:DEFINER|INVOKER)\s+)?' |
| 188 | . 'VIEW\b/i'; |
| 189 | |
| 190 | return (bool) preg_match($prefix, $head); |
| 191 | } |
| 192 | |
| 193 | /** |
| 194 | * The real start of a statement: a short piece from the front with comments removed and mysqldump's |
| 195 | * /*! ... */ wrappers unwrapped, so the first real word (CREATE / INSERT / SET / ...) is visible. |
| 196 | * Only used to tell statements apart, never to change one, so dropping the rest is fine. |
| 197 | */ |
| 198 | public static function effectiveHead(string $sql): string { |
| 199 | $s = substr($sql, 0, self::HEAD_WINDOW); |
| 200 | |
| 201 | $s = preg_replace('/--(?=[\s]).*?(?:\n|$)/s', ' ', $s); // drop -- comments |
| 202 | $s = preg_replace('/#.*?(?:\n|$)/s', ' ', $s); // drop # comments |
| 203 | $s = preg_replace('/\/\*(?!!).*?\*\//s', ' ', $s); // drop plain /* */ comments |
| 204 | $s = preg_replace('/\/\*!\d*/', ' ', $s); // unwrap /*! openers (keep their SQL) |
| 205 | $s = str_replace('*/', ' ', $s); // and drop the closers |
| 206 | $s = preg_replace('/[ \t\r\n]+/', ' ', $s); |
| 207 | |
| 208 | return ltrim((string) $s); |
| 209 | } |
| 210 | |
| 211 | /** |
| 212 | * Rewrite a CREATE VIEW so it restores on another server: drop DEFINER=/ALGORITHM=, force |
| 213 | * SQL SECURITY INVOKER, and make it CREATE OR REPLACE. Anything that isn't really a CREATE VIEW is |
| 214 | * returned unchanged - the isCreateView() check up front is what keeps it from ever mangling an |
| 215 | * INSERT that just happens to mention those words. |
| 216 | */ |
| 217 | public static function normalizeCreateView(string $sql): string { |
| 218 | if (!self::isCreateView($sql)) { |
| 219 | return $sql; |
| 220 | } |
| 221 | |
| 222 | $parts = preg_split('/\bAS\b/i', $sql, 2); |
| 223 | $header = $parts[0] ?? $sql; |
| 224 | $body = $parts[1] ?? ''; |
| 225 | |
| 226 | $header = preg_replace( |
| 227 | '/\/\*!\d+\s+DEFINER\s*=\s*[^*]+SQL\s+SECURITY\s+(?:DEFINER|INVOKER)\s*\*\//i', |
| 228 | ' ', |
| 229 | $header |
| 230 | ); |
| 231 | |
| 232 | $header = preg_replace( |
| 233 | '/\bDEFINER\s*=\s*(?:`[^`]+`@`[^`]+`|\'[^\']+\'@\'[^\']+\'|[^ \t\n\r\f\)]+)\s*/i', |
| 234 | ' ', |
| 235 | $header |
| 236 | ); |
| 237 | |
| 238 | $header = preg_replace('/\bALGORITHM\s*=\s*\w+\s*/i', ' ', $header); |
| 239 | |
| 240 | $header = preg_replace('/\bCREATE\s+(?!OR\s+REPLACE\b)/i', 'CREATE OR REPLACE ', $header, 1); |
| 241 | |
| 242 | if (preg_match('/\bSQL\s+SECURITY\s+(?:DEFINER|INVOKER)\b/i', $header)) { |
| 243 | $header = preg_replace('/\bSQL\s+SECURITY\s+(?:DEFINER|INVOKER)\b/i', 'SQL SECURITY INVOKER', $header, 1); |
| 244 | } else { |
| 245 | $header = preg_replace( |
| 246 | '/\b(CREATE\s+(?:OR\s+REPLACE\s+)?)(VIEW\b)/i', |
| 247 | '$1SQL SECURITY INVOKER $2', |
| 248 | $header, |
| 249 | 1 |
| 250 | ); |
| 251 | } |
| 252 | |
| 253 | $header = preg_replace('/[ \t]+/', ' ', $header); |
| 254 | $header = trim($header); |
| 255 | |
| 256 | if ($body === '') { |
| 257 | return $header; |
| 258 | } |
| 259 | return $header . ' AS' . (preg_match('/^\s/', $body) ? '' : ' ') . $body; |
| 260 | } |
| 261 | } |
| 262 |