Category Archives: SQL

SQL

SQL injection

SQL injection is a technique often used to attack databases through a website. This is done by including portions of SQL statements in a web form entry field in an attempt to get the website to pass a newly formed rogue SQL command to the database (e.g. dump the database contents to the attacker). SQL injection is a code injection technique that exploits a security vulnerability in a website’s software. The vulnerability happens when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and unexpectedly executed. SQL commands are thus injected from the web form into the database of an application (like queries) to change the database content or dump the database information like credit card or passwords to the attacker. SQL injection is mostly known as an attack vector for websites but can be used to attack any type of SQL database.

• SQL code is injected into the sql query

• Allows attacker to do almost anything the database user is permitted

• Example sql statement will return all the data from the ‘users’ table:

$sql = "SELECT * FROM users WHERE
username='$user' AND password='$pass'";
$user and $pass contain the value ' OR 1=1"

• Further attack possibilities: insert data, delete data, read data, denial of service…

Counter Measures

• Use prepared statements when supported by the database

• Use database-specific escaping functions when creating the sql statement ex: mysqli_real_escape_string()

• Addslashes() is not a sufficient approach

SQL

SQL ROUND()

SQL ROUND()  is used to round a numeric field to the number of decimals specified.

Syntaxt

SELECT ROUND(column_name,decimals) FROM table_name

Table – Users

+—–+—–+———–+
| oid | uid | price     |
+—–+—–+———–+
|   1 |   1 | 60.345645 |
|   2 |   2 | 30.45564  |
|   3 |   2 | 60.78565  |
|   4 |   1 | 70.96565  |
|   5 |   2 | 80.565645 |
|   6 |   1 | 50.57565  |
+—–+—–+———–+

Example

mysql> SELECT ROUND(price,2) as price  FROM orders;
+——-+
| price |
+——-+
| 60.35 |
| 30.46 |
| 60.79 |
| 70.97 |
| 80.57 |
| 50.58 |
+——-+
6 rows in set (0.00 sec)


SQL

SQL MID()

SQL MID()  is used to extract characters from a text field.

Syntaxt

SELECT MID(column_name,start,length) FROM table_name

Table – Users

+—–+——-+
| uid | name  |
+—–+——-+
|   1 | name1 |
|   2 | name2 |
|   3 | name3 |
|   4 | name4 |
+—–+——-+

Example

mysql> SELECT MID(name,1,3) as name  FROM users;
+——+
| name |
+——+
| nam  |
| nam  |
| nam  |
| nam  |
+——+
4 rows in set (0.00 sec)

mysql> SELECT MID(name,2,4) as name  FROM users;
+——+
| name |
+——+
| ame1 |
| ame2 |
| ame3 |
| ame4 |
+——+
4 rows in set (0.00 sec)

SQL

SQL LCASE()

SQL LCASE()  converts the value of a field to lowercase.

Syntaxt

SELECT LCASE(column_name) FROM table_name

Table – Users

+——+
| name |
+——+
| AAAA |
| BBBB |
| CCCC |
| DDDD |
| EEEE |
+——+

Example

mysql> SELECT LCASE(name) FROM users;
+————-+
| LCASE(name) |
+————-+
| aaaa        |
| bbbb        |
| cccc        |
| dddd        |
| eeee        |
+————-+
5 rows in set (0.00 sec)

SQL

SQL UCASE()

SQL UCASE()  converts the value of a field to uppercase.

Syntaxt

SELECT UCASE(column_name) FROM table_name

Table – Users

+—–+——+
| uid | name |
+—–+——+
|   1 | aaaa |
|   2 | bbbb |
|   3 | cccc |
|   4 | dddd |
|   5 | eeee |
+—–+——+

Example

mysql> SELECT UCASE(name) AS name  FROM users;
+——+
| name |
+——+
| AAAA |
| BBBB |
| CCCC |
| DDDD |
| EEEE |
+——+
5 rows in set (0.00 sec)

SQL

SQL HAVING

SQL HAVING clause is used with aggregate functions.

Syntaxt

SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

Table – Users

+—–+——+
| uid | name |
+—–+——+
|   1 | AAAA |
|   2 | BBBB |
|   3 | CCCC |
|   4 | DDDD |
|   5 | EEEE |
+—–+——+

Table – Orders

+—–+—–+——-+
| oid | uid | price |
+—–+—–+——-+
|   1 |   1 | 60    |
|   2 |   2 | 30    |
|   3 |   2 | 60    |
|   4 |   1 | 70    |
|   5 |   2 | 80    |
|   6 |   1 | 50    |
+—–+—–+——-+

Example

mysql> SELECT SUM(o.price) AS total,u.name FROM users AS u JOIN orders AS o ON u.uid=o.uid GROUP BY o.uid HAVING total >175;
+——-+——+
| total | name |
+——-+——+
|   180 | AAAA |
+——-+——+
1 row in set (0.00 sec)

SQL

SQL GROUP BY

SQL GROUP BY should be added with all aggregate functions and it s used in conjunction with the aggregate functions to group the result-set by one or more columns.

Table – Users

+—–+——+
| uid | name |
+—–+——+
|   1 | AAAA |
|   2 | BBBB |
|   3 | CCCC |
|   4 | DDDD |
|   5 | EEEE |
+—–+——+

Table – Orders

+—–+—–+——-+
| oid | uid | price |
+—–+—–+——-+
|   1 |   1 | 60    |
|   2 |   2 | 30    |
|   3 |   2 | 60    |
|   4 |   1 | 70    |
|   5 |   2 | 80    |
|   6 |   1 | 50    |
+—–+—–+——-+

Example

mysql> SELECT SUM(o.price) AS total,u.name FROM users AS u JOIN orders AS o ON u.uid=o.uid GROUP BY o.uid;;
+——-+——+
| total | name |
+——-+——+
| 180 | AAAA |
| 170 | BBBB |
+——-+——+
2 rows in set (0.00 sec)


SQL

SQL SUM()

SQL SUM()  returns the total sum of a numeric column.

Table – Students

+—–+——+——-+——-+——-+
| sid | name | mark1 | mark2 | mark3 |
+—–+——+——-+——-+——-+
|   1 | AAAA |    30 |    35 |    40 |
|   2 | BBBB |    36 |    37 |    38 |
|   3 | CCCC |    42 |    43 |    44 |
|   4 | DDDD |    43 |    32 |    39 |
+—–+——+——-+——-+——-+

Example

mysql> SELECT SUM(mark1) FROM students ;
+————+
| SUM(mark1) |
+————+
|        151 |
+————+
1 row in set (0.00 sec)

SQL

SQL MIN()

SQL MIN()  returns the smallest value of the selected column.

Table – Students

+—–+——+——-+——-+——-+
| sid | name | mark1 | mark2 | mark3 |
+—–+——+——-+——-+——-+
|   1 | AAAA |    30 |    35 |    40 |
|   2 | BBBB |    36 |    37 |    38 |
|   3 | CCCC |    42 |    43 |    44 |
|   4 | DDDD |    43 |    32 |    39 |
+—–+——+——-+——-+——-+

Example

mysql> SELECT MIN(mark1) FROM students ;
+————+
| MIN(mark1) |
+————+
|         30 |
+————+
1 row in set (0.00 sec)