Skip to content

Commit 297d854

Browse files
Seb35Seb35
committed
Initial version adapted from Java version
Java version: https://github.com/Orange-Cyberdefense/sqltrees
0 parents  commit 297d854

File tree

12 files changed

+2110
-0
lines changed

12 files changed

+2110
-0
lines changed

‎.gitignore‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
/vendor

‎LICENSE‎

Lines changed: 674 additions & 0 deletions
Large diffs are not rendered by default.

‎README.md‎

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,50 @@
1+
SQLTrees (php)
2+
==============
3+
4+
This is a PHP version of the Java library [SQLTrees](https://github.com/Orange-Cyberdefense/sqltrees) aiming at annihilating (possibility of) SQL injections and at the same time keeping the whole expressiveness of SQL. You can read (in French) [this introductory article](https://connect.ed-diamond.com/MISC/misc-111/zero-sqli-malgre-les-developpeurs), or read below a summary.
5+
6+
7+
Example
8+
=======
9+
10+
In the MySQL system database `information_schema`, take the following SQL request containing a parameter in the WHERE provided by the application to let the user jump at some point in the list:
11+
``lang=sql`
12+
SELECT table_schema, table_name, engine, table_rows, create_time FROM tables WHERE table_name >= 'parameter' ORDER BY table_name, create_time LIMIT 0,10;
13+
```
14+
15+
In a trivial example like this one, the request can be rewritten as a parametrised request:
16+
```lang=sql
17+
SELECT table_schema, table_name, engine, table_rows, create_time FROM tables WHERE table_name >= ? ORDER BY table_name, create_time LIMIT 0,10;
18+
-- with the parameters: ('parameter')
19+
``
20+
21+
But if now we want let the user choose the requested columns, we write in PHP:
22+
```lang=php
23+
function getParametrisedSQL( $columns, $parameter ) {
24+
return [
25+
'sql' => 'SELECT ' . implode( ', ', $columns ) . ' FROM tables WHERE tables_name >= ? ORDER BY table_name, create_time LIMIT 0,10;',
26+
'parameters' => [ $parameter ],
27+
];
28+
}
29+
```
30+
And now the name of the columns could introduce a SQL injection, if the columns names are not sufficiently sanitised beforehand, even if we use a parametrised SQL request.
31+
32+
During an audit, all functions manipulating some parts of SQL requests must be carefully examinated to be sure no one has some escaping issues.
33+
34+
**With this library**, the previous parametrised function would be rewritten like this:
35+
```lang=php
36+
use SQLTrees;
37+
38+
function getCompiledSQL( $columns, $parameter ) : CompiledStatement {
39+
return select( tupleArray( array_map( function( $column ) { return id( $column ); }, $columns ),
40+
from( id( 'tables' ) ),
41+
where( operator( id( 'table_name' ), '>=', str( $parameter ) ) ),
42+
order_by( tuple( id( 'table_name' ), id( 'create_time' ) ) ),
43+
limit( tuple( num( 0 ), num( 10 ) ) )
44+
)->compile();
45+
}
46+
47+
$conn = new mysqli( 'localhost', 'manager', 'password', 'information_schema' );
48+
$stmt = getCompiledSQL( $columns, $parameter )->run_mysqli( $conn );
49+
```
50+

‎composer.json‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,13 @@
1+
{
2+
"require": {
3+
"php": ">=7.3"
4+
},
5+
"autoload": {
6+
"psr-4": {
7+
"SQLTrees\\": "src/"
8+
},
9+
"files": [
10+
"src/functions.php"
11+
]
12+
}
13+
}

‎src/AST.php‎

Lines changed: 73 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,73 @@
1+
<?php
2+
3+
namespace SQLTrees;
4+
5+
use mysqli;
6+
7+
class AST {
8+
9+
protected $lp = '(';
10+
protected $rp = ')';
11+
protected $label;
12+
protected $parenLevel;
13+
14+
private $children;
15+
16+
function __construct( string $label, ?array $a, int $plevel ) {
17+
18+
$this->parenLevel = $plevel;
19+
$this->label = $label;
20+
$this->children = ( $a === null ) ? [] : $a;
21+
}
22+
23+
public function getChildrenCount() : int {
24+
25+
return count( $this->children );
26+
}
27+
28+
public function getChild( int $i ) : AST {
29+
30+
return $this->children[$i];
31+
}
32+
33+
protected function writeTo( CompiledStatement $b ) : void {
34+
35+
$b->addToken( $this->label );
36+
$n = $this->getChildrenCount();
37+
for( $i = 0; $i < $n; $i++ ) {
38+
$this->getChild( $i )->cpwriteTo( $b, $this->parenLevel );
39+
}
40+
}
41+
42+
protected function pwriteTo( CompiledStatement $b ) : void {
43+
44+
$b->addToken( $this->lp );
45+
$this->writeTo( $b );
46+
$b->addToken( $this->rp );
47+
}
48+
49+
protected function cpwriteTo( CompiledStatement $b, int $p ) : void {
50+
51+
if( $this->parenLevel < $p ) {
52+
$this->writeTo( $b );
53+
} else {
54+
$this->pwriteTo( $b );
55+
}
56+
}
57+
58+
/**
59+
* Compile an AST and return a CompiledStatement ready for execution.
60+
*
61+
* @return CompiledStatement
62+
*/
63+
public function compile() : CompiledStatement {
64+
65+
$b = new CompiledStatement();
66+
$this->writeTo( $b );
67+
return $b;
68+
}
69+
70+
public function execute_mysqli( mysqli $con ) {
71+
return $this->compile()->run_mysqli( $con );
72+
}
73+
}

‎src/CompiledStatement.php‎

Lines changed: 99 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,99 @@
1+
<?php
2+
3+
namespace SQLTrees;
4+
5+
use \mysqli;
6+
use \mysqli_stmt;
7+
8+
final class CompiledStatement {
9+
10+
/** @var string */
11+
private $tpl = '';
12+
/** @var string[] */
13+
private $args = [];
14+
/** @var string[] */
15+
private $types = [];
16+
17+
public function getTpl() : string {
18+
19+
return trim( $this->tpl ) . ';';
20+
}
21+
22+
public function getArgs() : array {
23+
24+
return $this->args;
25+
}
26+
27+
public function getTypes() : array {
28+
29+
return $this->types;
30+
}
31+
32+
/**
33+
* @param string $s Stringed value
34+
* @param string $type Original type of the value in [ 's', 'i', 'd', 'b' ]
35+
*/
36+
public function addParam( string $s, string $type ) : void {
37+
38+
$this->tpl .= '? ';
39+
$this->args[] = $s;
40+
$this->types[] = $type;
41+
}
42+
43+
public function addStringParam( string $s ) : void {
44+
45+
$this->tpl .= '? ';
46+
$this->args[] = $s;
47+
$this->types[] = 's';
48+
}
49+
50+
public function addToken( string $repr ) : void {
51+
52+
$this->tpl .= $repr;
53+
$this->tpl .= ' ';
54+
}
55+
56+
/**
57+
* Get a (compiled) prepared statement.
58+
*
59+
* The prepared statement was compiled from AST with one string and two arrays:
60+
* - string query: the template SQL query with ? as parameters,
61+
* - array args: the parameters of the template SQL query,
62+
* - array types: the types of the parameters.
63+
*
64+
* @param mysqli $con MySQL connection
65+
* @return mysqli_stmt MySQL prepared statement
66+
*/
67+
public function getPreparedStatement_mysqli( mysqli $con ) : mysqli_stmt {
68+
69+
$stmt = new mysqli_stmt( $con, $this->getTpl() );
70+
$types = implode( '', $this->types );
71+
if( $types ) {
72+
$stmt->bind_param( $types, ...$this->args );
73+
}
74+
return $stmt;
75+
}
76+
77+
/**
78+
* Execute a (compiled) prepared statement against a MySQL connection.
79+
*
80+
* A mysqli_stmt is returned to be able to read detailled errors and warnings.
81+
* Except error management, the following classical instructions could be
82+
* used to obtain the result:
83+
* $stmt = $ast->execute( $con );
84+
* $mysqlResult = $stmt->get_result();
85+
* $result = $mysqlResult->fetch_all();
86+
*
87+
* @param mysqli $con Mysql connection
88+
* @return mysqli_stmt Executed MySQL prepared statement
89+
* @throws CompiledStatementError When execution failed
90+
*/
91+
public function run_mysqli( mysqli $con ) : mysqli_stmt {
92+
93+
$stmt = $this->getPreparedStatement_mysqli( $con );
94+
if( !$stmt->execute() ) {
95+
throw new CompiledStatementError( 'Execution failed' );
96+
}
97+
return $stmt;
98+
}
99+
}

‎src/CompiledStatementError.php‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,7 @@
1+
<?php
2+
3+
namespace SQLTrees;
4+
5+
use \RuntimeException;
6+
7+
class CompiledStatementError extends RuntimeException {}

‎src/OperatorAST.php‎

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,32 @@
1+
<?php
2+
3+
namespace SQLTrees;
4+
5+
final class OperatorAST extends AST {
6+
7+
/** @var ?AST Neutral element, when the expressions array is empty. */
8+
protected $neutral;
9+
10+
function __construct( string $s, array $exprs, int $plevel, ?AST $neutral ) {
11+
12+
parent::__construct( $s, $exprs, $plevel );
13+
$this->neutral = $neutral;
14+
}
15+
16+
protected function writeTo( CompiledStatement $b ) : void {
17+
18+
$n = $this->getChildrenCount();
19+
20+
if( $n === 0 ) {
21+
if( $this->neutral ) {
22+
$this->neutral->writeTo( $b );
23+
}
24+
} else {
25+
$this->getChild( 0 )->cpwriteTo( $b, $this->parenLevel );
26+
for( $i = 1; $i < $n; $i++ ) {
27+
$b->addToken( $this->label );
28+
$this->getChild( $i )->cpwriteTo( $b, $this->parenLevel );
29+
}
30+
}
31+
}
32+
}

0 commit comments

Comments
 (0)