-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathdynamic-data.php
More file actions
138 lines (100 loc) · 3.48 KB
/
dynamic-data.php
File metadata and controls
138 lines (100 loc) · 3.48 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
<?php
//--------------------------------------------------
// Allow list
//--------------------------------------------------
// Unsafe input
$field_unsafe = (string) ($_GET['field'] ?? NULL);
//--------------------------------------------------
// Library
/**
* @param literal-string $input
*/
function require_literal_string(string $input): void {
echo $input . "\n";
}
//--------------------------------------------------
// Example 1
$fields = [
'name' => 'u.full_name',
'email' => 'u.email_address',
'address' => 'u.postal_address',
];
$sql = '... ORDER BY ' . ($fields[$field_unsafe] ?? 'u.full_name');
require_literal_string($sql);
//--------------------------------------------------
// Example 2
$fields = ['name', 'address', 'email'];
$field_id = array_search($field_unsafe, $fields);
$sql = '... ORDER BY ' . $fields[$field_id];
require_literal_string($sql);
//--------------------------------------------------
// Allow escaped
//--------------------------------------------------
// Library
// The only important bit is that $identifiers is
// applied after $sql is checked to be a `literal-string`
class db_identifiers {
/**
* @param literal-string $sql
* @param array<int, string|int> $parameters
* @param array<string, string> $identifiers
*/
public function query($sql, $parameters = [], $identifiers = []): void {
foreach ($identifiers as $name => $value) {
if (!preg_match('/^[a-z0-9_]+$/', strval($name))) {
throw new Exception('Invalid identifier name "' . $name . '"');
} else if (!preg_match('/^[a-z0-9_]+$/', $value)) {
throw new Exception('Invalid identifier value "' . $value . '"');
} else {
$sql = str_replace('{' . $name . '}', '`' . $value . '`', $sql);
}
}
echo $sql . "\n\n";
print_r($parameters);
}
/**
* @return literal-string
*/
public function placeholders(int $count): string {
$sql = '?';
for ($k = 1; $k < $count; $k++) {
$sql .= ',?';
}
return $sql;
}
}
$db = new db_identifiers();
//--------------------------------------------------
// Using
$parameters = [];
$where_sql = 't.deleted IS NULL';
$ids = [1, 2, 3]; // Imagine these are from a set of <input type="checkbox" name="ids[]" value="1" />
$parameters = array_merge($parameters, $ids);
$where_sql .= ' AND
t.id IN (' . $db->placeholders(count($ids)) . ')';
// While array_map('intval', $ids) can work, it's easy to forget.
// Instead, just use parameterised queries:
// Levi Morrison = https://stackoverflow.com/a/23641033/538216
// PDO Execute = https://www.php.net/manual/en/pdostatement.execute.php#example-1012
// Drupal Multiple Arguments = https://www.drupal.org/docs/7/security/writing-secure-code/database-access#s-multiple-arguments
$sql = '
SELECT
t.id,
t.{select_field}
FROM
{from_table} AS t
WHERE
' . $where_sql . '
ORDER BY
{order_field}';
$identifiers = [
'select_field' => (string) ($_GET['select_field'] ?? 'name'),
'from_table' => (string) ($_GET['from_table'] ?? 'user'),
'order_field' => (string) ($_GET['order_field'] ?? 'email'),
];
$db->query($sql, $parameters, $identifiers);
// This query is still dangerous, as it probably allows the
// attacker to read details they shouldn't be allowed to.
// But purely on a technical point of view, it's not an
// Injection Vulnerability.
?>