96 lines
2.7 KiB
Markdown
96 lines
2.7 KiB
Markdown
# SQL Injection
|
|
|
|
- Using SQL injection, attackers can
|
|
- Add new data
|
|
- Modify current data
|
|
- Delete Data
|
|
|
|
## Example 1
|
|
|
|
$username $password
|
|
```js
|
|
$sql = "SELECT * FROM users WHERE username = ' " . $username " ' AND password = ' " . $password . " ' ";
|
|
```
|
|
|
|
```sql
|
|
SELECT * FROM users WHERE username = 'fred' AND password = 'Fr3dRu13S';
|
|
```
|
|
**.** concatenates
|
|
|
|
An attacker could circumvent the authentication by entering ` OR 1=1 --` into the username field
|
|
- This would select all users from the database, since `1=1` is always true.
|
|
- The rest of the query is then commented out with `--`.
|
|
|
|
```SQL
|
|
SELECT * FROM users WHERE username = '' OR 1=1 -- ' AND password = 'Fr3dRu13s'
|
|
```
|
|
|
|
In this statement, it would select all data from `users`, since the where clause is always true.
|
|
|
|
## Example 2
|
|
|
|
```SQL
|
|
SELECT prodinfo FROM prodtable WHERE prodname = 'dvd'
|
|
```
|
|
|
|
By using `dvd '; DROP TABLE prodtable; --`, the query would look like
|
|
|
|
```SQL
|
|
SELECT prodinfo FROM prodtable WHERE prodname = 'dvd'; DROP TABLE prodtable; -- '
|
|
```
|
|
|
|
Which would remove the entire product table.
|
|
|
|
## Avoiding SQL Injection
|
|
|
|
To avoid SQL injection, user inputs must be sanitised. This is achieved by escaping all characters that could be misused in the DBMS.
|
|
However, application developers are not usually database experts - may not know which characters to be escaped.
|
|
|
|
### Parameterised Queries
|
|
|
|
The best way to avoid SQL injection is to use prepared statements.
|
|
|
|
```SQL
|
|
SELECT * FROM users WHERE username = ? AND password = ?
|
|
```
|
|
|
|
This query would then be sent to the DBMS with the values for the 2 variables.
|
|
The values of these 2 variables will then automatically be used in place of the `?`, and the DBMS will sanitise the content of the variables using its own resources. This ensures new vulnerabilities will be handled by the DBMS rather than the developers of the application.
|
|
|
|
However, some values cannot be sanitised.
|
|
|
|
```SQL
|
|
SELECT * FROM mytable WHERE id = 23
|
|
|
|
SELECT * FROM mytable WHERE id = 23 OR 1=1
|
|
```
|
|
|
|
### Other Defences
|
|
|
|
- Checking Syntax for validity
|
|
- Fixed formats, e.g email, dates, part numbers.
|
|
- Verify input is valid in context
|
|
- If possible, exclude quotes and semicolons
|
|
- Have length limits on input
|
|
- Many attacks depend on entering long strings.
|
|
|
|
# Chapter 37
|
|
|
|
## 1
|
|
Backup mechanism, make copies of database and log
|
|
Log File, keep track of state of transactions
|
|
Checkpoints
|
|
Recovery Manager
|
|
## 2
|
|
T2 Safe, commit before checkpoint
|
|
T3 ignored, still running
|
|
T1 redone, did not commit until after checkpoint
|
|
## 3
|
|
DBMS recovery is responsible for Durability and Atomicity
|
|
## 4
|
|
T1 committed after checkpoint, redo
|
|
T2 running at failure, undo
|
|
T3 committed, safe
|
|
T4 committed after checkpoint, redo
|
|
T5 running at failure, undo
|