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 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 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 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 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 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 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)
