· 7 years ago · Oct 12, 2018, 05:16 PM
1Adam's SQL Notes for crazy people:
2
3
4
5Four Basic SQL Operations, or CRUD
6
7Creating Data – Filling data into tables.
8Reading Data – Query data out of a table.
9Updating Data – Change data already in a table.
10Deleting Data – Remove data from the table.
11
12"CRUD†is considered the fundamental set of four basic functions or features that every database must have.
13
14These are the basic features that use these important SQL commands: `CREATE`, `INSERT`, `SELECT`, `UPDATE`, `DELETE`, and `DROP`.
15
16
17SQL Data Types
18
19Here’s a list of all the formal data types that ISO/IEC standard SQL recognizes. In addition to these, you may define additional data types that are derived from these.
20
21Exact Numerics:
22
23 INTEGER
24 SMALLINT
25 BIGINT
26 NUMERIC
27 DECIMAL
28
29Approximate Numerics:
30
31 REAL
32 DOUBLE PRECISION
33 FLOAT
34
35Binary Strings:
36
37 BINARY
38 BINARY VARYING
39 BINARY LARGE OBJECT
40
41Boolean:
42
43 BOOLEAN
44
45Character Strings:
46
47 CHARACTER
48 CHARACTER VARYING (VARCHAR)
49 CHARACTER LARGE OBJECT
50 NATIONAL CHARACTER
51 NATIONAL CHARACTER VARYING
52 NATIONAL CHARACTER LARGE OBJECT
53
54Datetimes:
55
56 DATE
57 TIME WITHOUT TIMEZONE
58 TIMESTAMP WITHOUT TIMEZONE
59 TIME WITH TIMEZONE
60 TIMESTAMP WITH TIMEZONE
61
62Intervals:
63
64 INTERVAL DAY
65 INTERVAL YEAR
66
67Collection Types:
68
69 ARRAY
70 MULTISET
71
72Other Types:
73
74 ROW
75 XML
76
77
78
79
80
81SQL Value Functions
82
83These SQL value functions perform operations on data. There are all kinds of operations that could conceivably be performed on data items, but these are some that are needed most often.
84
85String Value Functions
86
87Function Effect
88-------------------------------
89SUBSTRING Extracts a substring from a source string
90
91SUBSTRING SIMILAR Extracts a substring from a source string, using POSIX-based
92 regular expressions
93
94SUBSTRING_REGEX Extracts from a string the first occurrence of an XQuery
95 regular expression pattern and returns one occurrence of the
96 matching substring
97
98TRANSLATE_REGEX Extracts from a string the first or every occurrence of an
99 XQuery regular expression pattern and replaces it or them with an
100 XQuery replacement string
101
102UPPER Converts a character string to all uppercase
103
104LOWER Converts a character string to all lowercase
105
106TRIM Trims off leading or trailing blanks
107
108TRANSLATE Transforms a source string from one character set to
109 another
110
111CONVERT Transforms a source string from one character set to
112 another
113
114
115Numeric Value Functions
116
117Function Effect
118-------------------------------
119POSITION Returns the starting position of a target string within a
120 source string
121CHARACTER_LENGTH Returns the number of characters in a string
122
123OCTET_LENGTH Returns the number of octets (bytes) in a character string
124
125EXTRACT Extracts a single field from a datetime or interval
126
127
128Datetime Value Functions
129
130Function Effect
131-------------------------------
132CURRENT_DATE Returns the current date
133
134CURRENT_TIME(p) Returns the current time; (p) is precision of seconds
135
136CURRENT_TIMESTAMP(p) Returns the current date and the current time; (p) is precision
137 of seconds
138
139
140
141SQL Set Functions
142-------------------------------
143The SQL set functions give you a quick answer to questions you may have about the characteristics of your data as a whole. How many rows does a table have? What is the highest value in the table? What is the lowest? These are the kinds of questions that the SQL set functions can answer for you.
144
145COUNT Returns the number of rows in the specified table
146
147MAX Returns the maximum value that occurs in the specified
148 table
149
150MIN Returns the minimum value that occurs in the specified
151 table
152SUM Adds up the values in a specified column
153
154AVG Returns the average of all the values in the specified
155 column
156
157
158SQL WHERE Clause Predicates
159------------------------------
160Predicates boil down to either a TRUE or a FALSE result. You can filter out unwanted rows from the result of an SQL query by applying a WHERE clause whose predicate excludes the unwanted rows.
161
162Comparison Predicates
163
164= Equal
165<> Not equal
166< Less than
167<= Less than or equal
168> Greater than
169>= Greater than or equal
170
171Other Predicates
172
173ALL BETWEEN
174DISTINCT EXISTS
175IN LIKE
176MATCH NOT IN
177NOT LIKE NULL
178OVERLAPS SIMILAR
179SOME, ANY UNIQUE
180
181
182
183
184Introduction
185
186Nowadays, web applications are common around the world. Nearly every major company or organization has a web presence. Maximum of these companies and organizations use web applications to provide various services to users. Some of these web applications employ database driven content. The back-end database often contains confidential and sensitive information such security numbers, credit card number, financial data, medical data. Typically the web user supplies information, such as a username and password and web applications receive user request and interact with the back-end database and returned relevant data to the user.
187What is SQL injection?
188
189SQL injection is a technique (like other web attack mechanisms) to attack data driven applications. This attack can bypass a firewall and can affect a fully patched system. The attacker takes the advantage of poorly filtered or not correctly escaped characters embedded in SQL statements into parsing variable data from user input. The attacker injects arbitrary data, most often a database query, into a string that’s eventually executed by the database through a web application (e.g. a login form).
190
191
192Through SQL Injection attacker can obtain unauthorized access to a database and can create, read, update, alter, or delete data stored in the back-end database. Currently, almost all SQL databases such as Oracle, MySQL, PostgreSQL, MSSQL Server, MS Access are potentially vulnerable to SQL injection attacks. In its most common form, a SQL injection attack gives access to sensitive information such as social security numbers, credit card number or other financial data.
193
194
195
196
197
198Why SQL injection?
199
200- Identify injectable parameters.
201- Identify the database type and version.
202- Discover database schema.
203- Extracting data.
204- Insert, modify or delete data.
205- Denial of service to authorized users by locking or deleting tables.
206- Bypassing authentication.
207- Privilege escalation.
208- Execute remote commands by calling stored functions within the DBMS which are reserved for administrators.
209
210
211
212
213
214SQL injection method
215
216Here are some methods through which SQL statements are injected into vulnerable systems
217- Injected through user input.
218- Injection through cookie fields contains attack strings.
219- Injection through Server Variables.
220- Second-Order Injection where hidden statements to be executed at another time by another function.
221
222
223
224
225
226
227Vulnerabilities
228
229In computer security, a vulnerability is a weakness which allows an attacker to reduce a system's information assurance. Web-based forms allow some access to the back-end database to allow entry of data and a response, this kind of attack bypasses firewalls and endpoint defenses. Any web form, even a simple logon form or search box (where a user can input data), might provide access to back-end database by means of SQL injection.
230vulnerabilities SQL injection
231
232The common reason that an application is vulnerable to SQL injection is improper filtering and lack of validation for user input. Input forms are quite common to collect data from a user. So, practically it is not feasible to close all the entry points to bar SQL injection. To prevent attacks developers must apply proper filtration/validation on all forms.
233Vulnerable Applications
234
235– Almost all SQL databases are potentially vulnerable such as MS SQL Server, DB2, Oracle, PostgreSQL, MySQL, MS Access, Sybase, Informix, etc
236
237– Accessed through applications using :
238 ASP, JSP, PHP
239 Perl and CGI scripts that access databases
240 XML, XSL and XSQL
241 JavaScript
242 Database specific web applications
243 Many other
244
245
246
247
248
249Types of SQL injection
250
251-- Tautology-based SQL Injection
252
253-- Piggy-backed Queries / Statement Injection
254
255-- Union Query
256
257-- Illegal/Logically Incorrect Queries
258
259-- Inference
260
261-- Stored Procedure Injection
262
263
264
265
266
267
268Tautologies
269
270Purpose :
271
272 Identify injectable parameters
273 Bypass authentication
274 Extract data
275
276In logic, a tautology (from the Greek word ταυτολογία) is a formula which is true in every possible interpretation. In a tautology-based attack, the code is injected using the conditional OR operator such that the query always evaluates to TRUE. Tautology-based SQL injection attacks are usually bypass user authentication and extract data by inserting a tautology in the WHERE clause of a SQL query. The query transform the original condition into a tautology, causes all the rows in the database table are open to an unauthorized user. A typical SQL tautology has the form "or <comparison expression>", where the comparison expression uses one or more relational operators to compare operands and generate an always true condition. If an unauthorized user input user id as abcd and password as anything' or 'x'='x then the resulting query will be:
277
278select * from user_details where userid = 'abcd' and password = 'anything' or 'x'='x'
279
280
281
282
283Piggy-backed Queries / Statement Injection
284
285Purpose :
286
287 Extract data
288 Modify dataset
289 Execute remote commands
290 Denial of service
291
292This type of attack is different than others because the hacker injects additional queries to the original query, as a result the database receives multiple SQL queries. The first query is valid and executed normally, the subsequent queries are the injected queries, which are executed in addition to the first. Due to misconfiguration, a system is vulnerable to piggy-backed queries and allows multiple statements in one query. Let an attacker inputs abcd as usrerid and '; drop table xyz -- as password in the login form :
293Piggy-backed Queries
294
295Then the application will generate the following query :
296
297select * from user_details where userid = 'abcd' and password = ''; drop table xyz -- '
298
299After completing the ï¬rst query ( returned an empty result set (i.e. zero rows)), the database would recognize the query delimiter(";") and execute the injected second query. The result of executing the second query would be to drop table xyz, which would destroy valuable information.
300
301
302
303
304
305
306Union Query
307
308Purpose:
309
310 Bypassing authentication
311 Extract data
312
313This type of attack can be done by inserting a UNION query into a vulnerable parameter which returns a dataset that is the union of the result of the original first query and the results of the injected query.
314
315The SQL UNION operator combines the results of two or more queries and makes a result set which includes fetched rows from the participating queries in the UNION.
316
317Basic rules for combining two or more queries using UNION:
318
3191) A number of columns and order of columns of all queries must be same.
320
3212) The data types of the columns on involving table in each query should be same or compatible.
322
3233) Usually returned column names are taken from the first query.
324
325By default the UNION behaves like UNION [DISTINCT] , i.e. eliminated the duplicate rows; however, using ALL keyword with UNION returns all rows, including duplicates.
326The attacker who tries to use this method must have solid knowledge of DB schema.
327
328The two dashes (--) comments out the rest of the query i.e. ' and password = 'abcd'. Therefore, the query becomes the union of two SELECT queries. The first SELECT query returns a null set because there is no matching record in the table user_details. The second query returns all the data from the table emp_details. Let try it with our login form.
329union login
330
331
332
333
334
335
336Illegal/Logically Incorrect Queries
337
338Purpose :
339
340 Identify injectable parameters
341 Identify database
342 Extract data
343
344In this type of injection an attacker is trying gather information about the type and structure of the back-end database of a Web application. The attack is considered as a preliminary step for further attacks. If an incorrect query is sent to a database, some application servers return the default error message and the attacker takes the advantage of this weakness. They inject code in vulnerable or injectable parameters which creates syntax, type conversion, or logical error. Through type error, one can identify the data types of certain columns. Logical error often exposes the names of tables and columns.
345
346
347
348
349
350
351Inference
352
353Purpose :
354
355 Identify injectable parameters
356 Identify schema
357 Extract data
358
359In this type of injection, the attack is applied on well-secured databases which do not return any usable feedback or descriptive error messages. The attack is normally created in the style of the true false statement. After finding the vulnerable parameter, the attacker injects various conditions (that he wants to know whether they are true or false) through query and carefully observe the situation. If statement evaluates to true, the page continues to function normally. If false, the page behaves significantly different from the normally functioning. This type of injection is called Blind Injection. There is another type of inference attack which is called Time Attack. In this method, an attacker designs a conditional statement and inject through the vulnerable parameter and gather information based on time delays in the response of the database. See the following code:
360
361http://www.example.com/product.php?product_id=100 AND IF(version() like ‘5%’, sleep(15), ‘false’))--
362
363Here an attacker checks whether the system is using a MySQL version is 5.x or not, making the server delay the answer in 15 seconds (the attacker can increase the delay's time).
364
365
366
367
368
369Stored Procedures
370
371Purpose :
372
373 Privilege escalation
374 Denial of service
375 Execute remote commands
376
377A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure is actually stored in the database data dictionary. Typical use for stored procedures includes data validation or access control mechanisms. Furthermore, stored procedures can consolidate and centralize logic that was originally implemented in applications. Extensive or complex processing that requires execution of several SQL statements is moved into stored procedures, and all applications call the procedures. One can use nested stored procedures by executing one stored procedure from within another.
378Stored procedures type of SQL injection tries to execute stored procedures present in the database. Most of the database have a standard set of procedures (apart from user defined procedures) that extend the functionality of the database and allow for interaction with the operating system. The attacker initially tries to find the database type with another injection method like illegal/logically incorrect queries. Once an attacker determines which databases is used in backend then he try to execute various procedures through injected code. As the stored procedure are written by developers, therefore these procedures do not make the database vulnerable to SQL injection attacks. Stored procedures can be vulnerable to execute remote commands, privilege escalation, buffer overflows, and even provide administrative access to the operating system.
379If an attacker injects ';SHUTDOWN; -- into either the User ID or Password fields then it will generate the following SQL code :
380
381select * from user_details where userid = 'abcd' and password = ''; SHUTDOWN; -- '
382
383The above command causes a database to shut down.
384
385
386
387Alternate Encodings
388
389Purpose:
390
391 Evade Detection
392
393In this case, the attacker injected encoded text to bypass defensive coding practices. Attackers have arranged alternate methods of encoding through their injected strings such as using hexadecimal, ASCII,
394and Unicode character encoding. Scanning and detection techniques are not fully effective against alternate encodings. See the following example :
395
396SELECT * FROM users WHERE login= '' AND pass=' ';exec(char(Ox73687574646j776e)) '
397
398In the above code the char() function and ASCII hexadecimal encoding have used. The char() function returns the actual character(s) of hexadecimal encoding of character(s). This encoded string is translated into the shutdown command by the database when it is executed.
399
400-----------------------------------------------------------------------------------
401
402
403
404
405
406Defending against SQL Injection
407
408
409Researchers and security managers have proposed various defensive methods to fight against SQL injection attack. The root cause of almost every SQL injection is invalid input checking. Here is a list of prevention methods :
410
411 Input Validation
412 Input Checking Functions
413 Validate Input Sources
414 Access Rights
415 Configure database error reporting
416
417
418
419
420Input Validation
421
422– Simple input check can prevent many attacks.
423– Always validate user input by checking type, size, length, format, and range.
424– Test the content of string variables and accept only expected values.
425– Reject entries that contain binary data, escape sequences etc. This can help prevent script injection and can protect against some buffer overrun exploits.
426– When you are working with XML documents, validate all data against its schema as it is entered.
427
428
429
430Input Checking Functions
431
432– Certain characters and character sequences such as ; , --, select, insert and xp_ can be used to perform an SQL injection attack.
433– Remove these characters and character sequences from user input which reduces the chance of an injection attack.
434– Scan query string for undesirable word like "insert", "update", "delete", "drop" etc. check whether it represent a statement or valid user input.
435– Write a function which can handle all of this.
436
437
438List of the characters which are used to perform an SQL injection attack :
439Input character Meaning in SQL
440------------------------------------
441; Query delimiter.
442
443' Character data string delimiter.
444
445-- Comment delimiter.
446
447/* ... */ Comment delimiters. The text between /* and */ is not evaluated by the server.
448
449xp_ Used at the start of the name of catalog-extended stored procedures, such as xp_cmdshell.
450
451
452
453
454Validate Input Sources
455
456– There are so many ways to attack a database, therefore the developer should check and authenticate all input sources and disallow unidentified or untrusted users/websites.
457Access Rights/User Permissions
458
459– Create "low privileged" accounts for use by applications.
460– Never grant instanceÂlevel privileges to database accounts.
461– Never grant databaseÂowner or schemaÂowner privileges to database accounts.
462– Be aware of the permission scheme of your database.
463Configure database error reporting
464
465– Some application server's default error reporting often gives away information that is valuable for attackers (table name, field name, etc.).
466– The developer should configure the system correctly, therefore this information will never expose to an unauthorized user.
467
468Apart from the above, there are several methods which can prevent from SQL injection.
469
470Reference : http://en.wikipedia.org/wiki/SQL_injection
471
472
473
474
475
476
477=============================================================================
478SCRAPHEAP: DO NOT INCLUDE IN SLIDES/NOTES YET
479
480
481Union-Based SQL Injection
482It is the most popular type of SQL injection. This type of attack uses the UNION statement, which is the integration of two select statements, to obtain data from the database.
483
484Error-Based SQL Injection
485An error-based SQL injection is the simplest type; but, the only difficulty with this method is that it runs only with MS-SQL Server. In this attack, we cause an application to show an error to extract the database. Normally, you ask a question to the database, and it responds with an error including the data you asked for.
486
487Blind SQL Injection
488The blind SQL injection is the hardest type. In this attack, no error messages are received from the database; hence, we extract the data by asking questions to the database. The blind SQL injection is further divided into two kinds:
4891. Boolean-based SQL injection
4902. Time-based SQL injection
491The above techniques can be used to obtain the data in the database by either asking a question or inducing a time delay.
492
493
494SQL Injection types
495Error based Injection:
496
497The attacker sends some malicious query to the database which results in errors. The errors should be very generic, otherwise, they may give useful hints to the attacker.
498
499Comment-Line: Using comment line to cause the database to ignore a part of a valid query.
500
501E.g. Select * from stores where product_id = blah’ or 1=1-- (everything after this will be neglected)
502
503Tautology: There are a lot of strings which always evaluates to be true, like ‘1’ = ‘1’ ‘a’ = ‘a’, etc., using them in the query to create constantly true conditions.
504
505E.g. Select * from users where username=’blah’ or ‘a’=’a’ -- and password=’pass’
506Union Based SQL injection:
507
508Using union command in SQL query to execute additional queries; thereby, modifying/inserting/deleting or dropping the contents of the table.
509
510E.g. Select * from stores where product_id=1 union select 1,database(),user(),4#
511
512Stored procedures: Creating malicious inputs to execute malicious queries.
513
514Incorrect queries: Coming up with logically incorrect queries to see the error messages to get more information about the target database.
515
516Select * from stores where id=1’
517
518The above query will result in a syntax error and might reveal the backend database type.
519Blind SQL injection:
520
521This is a type of SQL injection where we don’t have a clue as to whether the web application is vulnerable to injection attack or not.
522Types:
523
524Boolean: Only correct queries show the result, wrong queries do not return anything. Attackers should try to generate logically correct queries.
525
526boolean type
527
528If suppose the original query to the database is
529
530Select * from users where id=’id.txt’
531
532If we give blah’ and 1=1# as input which evaluates to be a right query
533
534Select * from users where id=’blah’ or 1=1#, we will see the user results.
535
536If we give blah’ and 1=2# as input which is a wrong query then we don’t see any results.
537
538Select * from users where id=’blah’ or 1=2#
539
540Time delay: Depending on some conditions, setting a time delay. If that condition is satisfied, we can observe the time delay; thereby, concluding that the input we gave produced a positive result. This is a time consuming process.