Overview
Comment: | SQLite3: Replace userSelectStatemnt with a PHP8-compatible protectSelect method |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | 7.3 |
Files: | files | file ages | folders |
SHA1: |
81f80a8cc501fa1b08cab71c7dfb16ac |
User & Date: | bohwaz on 2020-11-06 01:00:28 |
Other Links: | branch diff | manifest | tags |
Context
2020-11-06
| ||
01:01 | SQLite3: remove debug check-in: c16f28a757 user: bohwaz tags: 7.3 | |
01:00 | SQLite3: Replace userSelectStatemnt with a PHP8-compatible protectSelect method check-in: 81f80a8cc5 user: bohwaz tags: 7.3 | |
2020-11-04
| ||
01:10 | Smartyer: implement closing compile blocks, finish partial custom block implementation check-in: cb25b478ff user: bohwaz tags: 7.3 | |
Changes
Modified src/lib/KD2/DB/SQLite3.php from [ca9f084903] to [3bf15fa5ff].
︙ | ︙ | |||
206 207 208 209 210 211 212 213 214 215 216 217 218 219 | $arg = $arg->format(self::DATE_FORMAT); return \SQLITE3_TEXT; } default: throw new \InvalidArgumentException('Argument '.$name.' is of invalid type '.gettype($arg)); } } /** * Executes a prepared query using $args array * @return \SQLite3Stmt|boolean Returns a boolean if the query is writing * to the database, or a statement if it's a read-only query. * * The fact that this method returns a boolean is voluntary, to avoid a bug | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 | $arg = $arg->format(self::DATE_FORMAT); return \SQLITE3_TEXT; } default: throw new \InvalidArgumentException('Argument '.$name.' is of invalid type '.gettype($arg)); } } /** * Returns a statement after having checked a query is a SELECT, * doesn't seem to contain anything that could help an attacker, * and if $allowed is not NULL, will try to restrict the query to tables * specified as array keys, and to columns (PHP8+ only) of these tables. * * Note that before PHP8+ this is less secure and doesn't restrict columns. * * @param array $allowed List of allowed tables and columns * @param string $query SQL query * @return \SQLite3Stmt */ public function protectSelect(?array $allowed, string $query) { if (preg_match('/;\s*(.+?)$/', $query)) { throw new \LogicException('Only one single statement can be executed at the same time.'); } // Forbid use of some strings that could give hints to an attacker: // PRAGMA, sqlite_version(), sqlite_master table, comments if (preg_match('/PRAGMA\s+|sqlite_version|sqlite_master|--|\/\*|\*\/|load_extension|ATTACH\s+|randomblob|sqlite_compileoption_|sqlite_offset|sqlite_source_|zeroblob|X\'\w|0x\w|sqlite_dbpage|fts3_tokenizer/i', $query, $match)) { throw new \LogicException('Invalid SQL query.'); } if (null !== $allowed) { // PHP 8+ if (method_exists($this->db, 'setAuthorizer')) { $this->db->setAuthorizer(function (int $action, ...$args) use ($allowed) { if ($action !== SQLite3::READ) { return SQLite3::DENY; } list($table, $column) = $args; if (!array_key_exists($table, $allowed)) { return SQLite3::DENY; } if (null !== $allowed[$table] && !in_array($column, $allowed[$table])) { return SQLite3::IGNORE; } return SQLite3::OK; }); } else { static $forbidden = ['ALTER', 'ADD', 'ATTACH', 'CREATE', 'COMMIT', 'CREATE', 'DELETE', 'DETACH', 'DROP', 'INSERT', 'PRAGMA', 'REINDEX', 'RENAME', 'REPLACE', 'ROLLBACK', 'SAVEPOINT', 'SET', 'TRIGGER', 'UPDATE', 'VACUUM', 'WITH']; $parsed = $this->parseQuery($query); foreach ($parsed as $keyword) { if (in_array($keyword, $forbidden)) { throw new \RuntimeException('Unauthorized keyword: ' . $keyword); } foreach ($keyword->tables as $table) { if (!array_key_exists($table, $allowed)) { throw new \RuntimeException('Unauthorized table: ' . $table); } if (null !== $allowed[$table]) { //throw new \InvalidArgumentException('Cannot protect columns without PHP 8+'); } } } } } $st = $this->db->prepare($query); if (!$st->readOnly()) { throw new \LogicException('Only read-only queries are accepted.'); } if (method_exists($this->db, 'setAuthorizer')) { $this->db->setAuthorizer(null); } return $st; } public function parseQuery(string $query): array { static $keywords = 'ABORT ACTION ADD AFTER ALL ALTER ALWAYS ANALYZE AND AS ASC ATTACH AUTOINCREMENT BEFORE BEGIN BETWEEN BY CASCADE CASE CAST CHECK COLLATE COLUMN COMMIT CONFLICT CONSTRAINT CREATE CROSS CURRENT CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP DATABASE DEFAULT DEFERRABLE DEFERRED DELETE DESC DETACH DISTINCT DO DROP EACH ELSE END ESCAPE EXCEPT EXCLUDE EXCLUSIVE EXISTS EXPLAIN FAIL FILTER FIRST FOLLOWING FOR FOREIGN FROM FULL GENERATED GLOB GROUP GROUPS HAVING IF IGNORE IMMEDIATE IN INDEX INDEXED INITIALLY INNER INSERT INSTEAD INTERSECT INTO IS ISNULL JOIN KEY LAST LEFT LIKE LIMIT MATCH NATURAL NO NOT NOTHING NOTNULL NULL NULLS OF OFFSET ON OR ORDER OTHERS OUTER OVER PARTITION PLAN PRAGMA PRECEDING PRIMARY QUERY RAISE RANGE RECURSIVE REFERENCES REGEXP REINDEX RELEASE RENAME REPLACE RESTRICT RIGHT ROLLBACK ROW ROWS SAVEPOINT SELECT SET TABLE TEMP TEMPORARY THEN TIES TO TRANSACTION TRIGGER UNBOUNDED UNION UNIQUE UPDATE USING VACUUM VALUES VIEW VIRTUAL WHEN WHERE WINDOW WITH WITHOUT'; $keywords = explode(' ', $keywords); $keywords = str_replace(' ', '|', $keywords); $query = rtrim($query, ';'); preg_match_all('/((["\'])(?:\\\2|.)*?\2|\b(?:' . implode('|', $keywords) . ')\b|[\w]+(?:\s*\.\s*[\w]+)*)/ims', $query, $match); $current = null; $query = []; foreach ($match[0] as $v) { $kw = strtoupper($v); if (in_array($kw, $keywords)) { $query[$kw] = (object) ['tables' => [], 'content' => []]; $current = $kw; } elseif (null !== $current) { if ($current == 'FROM' || $current == 'JOIN') { $query[$current]->tables[] = $v; } else { $query[$current]->content[] = $v; } } } return $query; } /** * Executes a prepared query using $args array * @return \SQLite3Stmt|boolean Returns a boolean if the query is writing * to the database, or a statement if it's a read-only query. * * The fact that this method returns a boolean is voluntary, to avoid a bug |
︙ | ︙ | |||
295 296 297 298 299 300 301 | public function query(string $statement) { $this->connect(); $statement = $this->applyTablePrefix($statement); return $this->db->query($statement); } | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < | 412 413 414 415 416 417 418 419 420 421 422 423 424 425 | public function query(string $statement) { $this->connect(); $statement = $this->applyTablePrefix($statement); return $this->db->query($statement); } public function iterate(string $statement, ...$args): iterable { $res = $this->preparedQuery($statement, ...$args); while ($row = $res->fetchArray(\SQLITE3_ASSOC)) { yield (object) $row; |
︙ | ︙ |