SQL orientation
SQL Orientation
- SQL Databases are made up of tables. Tables are created by the developer or architect and are empty on creation. Similar to a spreadsheet, a table's properties are defined by its columns. Columns can be different data types (strings, integers, booleans, floats, binary, etc).
- Not everything that works in the SQL console for the affected databasing engine will work with the language connector used by a vulnerable application.
The MySQL developers made database navigation fairly intuitive. Show databases:
- Displays a list of databases that the current user has access to
SHOW DATABASES; |
- Displays a list of table names in the currently selected database (if no database was specified), or a list of tables in the specified database
SHOW TABLES IN information_schema; |
- Displays a list of column names in the chosen table:
SHOW FIELDS IN information_schema.routines; |
PostgreSQL
- \l - Lists all databases
- \c [database] - Connect to database
- \dn - Lists all schemas in the current database
- \dt - Lists all tables in the current database
- \d [table_name]- Lists columns in table in the current database
Basic Queries
SELECT - Select data from a table
|
SELECT statements can contain clauses such as "WHERE", "LIMIT", "ORDER BY" and "GROUP BY" to find values that match specified patterns and filter results sets. |
- The basic syntax of a SELECT statement:
SELECT [column_name(s)] FROM [database_name(s)].[TABLE_NAME(s)] WHERE [condition] GROUP BY [column_name] ORDER BY [column_name] [ASC|DESC] LIMIT [ROW COUNT] OFFSET [START ON ROW] ->SELECT can be used with statements such as "WHERE", "LIMIT", "ORDER BY" and "GROUP BY" to find values that match specified patterns. |
For example, let's do a simple SELECT query on the following table (named "People" for our example).
+----------------------+ |ID| NAME |STATE | +----------------------+ |1 | John Doe |New York| +--+----------+--------+ |2 | Jane Doe |Florida | +--+----------+--------+
Let's perform a SELECT Query on our "People" Table, for the column "state".
SELECT state FROM people; |
You should get an output similar to the following:
+---------+ |STATE | +---------+ |New York | +---------+ |Florida | +---------+
Where
Lets demonstrate the use of a simple WHERE clause.
SELECT name FROM people WHERE id > 3; |
The above command would logically would be "select the name, from the rows in people where id is greater than three."; Which in this case would return nothing, because we only have IDs 1 and 2.
Let's say we added some new data to our table. it now looks like this:
+----------------------+ |ID| NAME |STATE | +--------------+-------+ |1 | John Doe |New York| +--+-----------+--------+ |2 | Jane Doe |Florida | +--+-----------+--------+ |3 | Billy Bob |New York| +--+-----------+--------+
Group by
The GROUP BY clause, groups results by column, and returns rows with unique values in the secified column.
SELECT name FROM people WHERE id > 0 GROUP BY state; |
would output
+-----------+ | NAME | +-----------+ | John Doe | +-----------+ | Jane Doe | +-----------+
Order by
The order by clause will order the rows, by a value in a column, ascending or descending (ASC/DESC).
If we have this table:
+------------------------+ |AGE| NAME | STATE | +--------------+---------+ |22 | John Doe | New York| +--+-----------+---------+ |31 | Billy Bob |New York| +--+-----------+---------+ |26 | Jane Doe | Florida | +--+-----------+---------+
and we ran the command
SELECT * FROM people ORDER BY age ASC; |
the output would show
+------------------------+ |AGE| NAME | STATE | +--------------+---------+ |22 | John Doe | New York| +--+-----------+---------+ |26 | Jane Doe | Florida | +--+-----------+---------+ |31 | Billy Bob |New York| +--+-----------+---------+
Limit
The LIMIT clause is very simple. it limits your results.
SELECT * FROM state WHERE age > 22 LIMIT 1; |
Would return the following:
+------------------------+ |AGE| NAME | STATE | +--+-----------+---------+ |26 | Jane Doe | Florida | +--+-----------+---------+
Multiple clauses
Now that we know how the Limit, Order By, Group By, and Where clauses work, let's roll it all into one.
If we ran this command on our current table:
SELECT * FROM people WHERE age < 31 GROUP BY state ORDER BY age LIMIT 1; |
+--------------+---------+ |22 | John Doe | New York| +--+-----------+---------+
UPDATE - Modify rows in a table
The UPDATE command is used to update specific rows in a table with a new value. It has the ability to alter a large amount of data with a single query, and as such can be a very dangerous command when access to it is granted to the wrong people.
For example:
UPDATE customers SET age=20 WHERE name='Richard' |
This will set the value of the 'name' row in the 'age' column to 20 wherever 'name' is 'Richard'.
Executing this query in an interactive environment will return the number of rows that were altered. If the WHERE clause is omitted, every row in the named table will be edited in accordance with this query.
Updating multiple columns
It is possible to alter the contents of multiple columns in a table with a single UPDATE query in the following manner:
UPDATE customer SET name='Richard' AND age='20' AND paid='yes' WHERE id='4' |
INSERT - Add rows to a table
The basic format of the INSERT statement is:
INSERT INTO TABLE (COLUMN, COLUMN, COLUMN) VALUES (VALUE, VALUE, VALUE) |
The number of columns and values must be the same.
It is similar to the UPDATE statement in that it allows you to alter the contents of entries in a table. However, the INSERT statement allows you to add a new row to the table specified, inserting data into whichever columns you choose (with a minimum of one) when you initialise it. Any columns not specified are simply left blank.
For example:
INSERT INTO customers (name, age, paid) VALUES ('Richard', '23', 'yes') |
DELETE - Delete rows from a table
The format of the DELETE statement is:
DELETE FROM TABLE WHERE COLUMN=VALUE |
This will delete a row from a table where the column is equal to the value specified. It is relatively simple to use, for example:
DELETE FROM customers WHERE age='20' |
