IT 467 midterm - SQL and firewall zone policy

What are some ways we can use information_schema to help us?

All of the tables in the DBMS are described in information_schema. We can use something like select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA ='someSchema. Columns can be found the same way: select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='someSchema

What is information_schema and why is it useful?

Information_schema is a special, read-only schema that contains information on every other schema (database) in the DBMS. It has a well-documented structure and is usually consistent across different database platforms. We can use it to find the names of schemata, the names of columns, the type of values held in those columns, and other data that is very important to us when trying to SQL inject.

What is the easiest way to prevent SQL injection?

Parameterized queries/prepared statements. A parameterized query takes a SQL statement and pre-compiles it on the server, so that only the paramters need to be supplied for the statement to execute. First the statement itself is prepared like so: $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)"); Parameters are then bound to the statement: $stmt->bindParam(':name', $name); $stmt->bindParam(':value', $value); All that is left is to execute the statement: $name = 'one'; $value = 1; $stmt->execute(); Here we fetch data from the user using parameterized queries: $stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?"); if ($stmt->execute(array($_GET['name']))) { while ($row = $stmt->fetch()) { print_r($row); } } Because the user data is wrapped in quotes, there is no risk of a SQL injection attack. The data is treated as a string, so if the user tries to enter some SQL syntax, the entire entry is treated as the name.

How does SELECT work?

SELECT returns a table containing zero or more rows of data. It either returns the number of columns specified, or the number of columns in the table if "*" is specified by the query. WHERE can be used to limit the results to match the clause.

How does UNION work? Why is it useful?

UNION combines the results of two SELECT queries, but only if the two queries have the same number of columns and the columns are type-compatible. The UNION allows us to essentially replace the query on the webpage with our own, by making it match no rows. We can do this with a WHERE clause that will never succeed.

What is the firewall zone policy? How do we configure it?

We divide the network up into three zones: the public, insecure Internet, the DMZ, and the trusted network (TN). We can then create rules based on each zone, and route traffic between these security zones. Rather than having to create multiple access lists for inbound and/or outbound traffic and assign them to different interfaces - a hefty administrative burden - we simply assign each zone to an interface. Once we have our 3 zones, we create zone pairs that will be used to determine how traffic is routed. These will be the Internet to the DMZ, the DMZ to the Internet, the TN to the DMZ, and the DMZ to the TN. While we could go from the Internet directly to the trusted network (or vice versa), this is very unsafe. Packets travel in both directions; if we create a rule for outbound traffic from the trusted network to the Internet, a malicious attacker could use that open port to jump directly into the trusted network and wreak havoc. We always want to go through the DMZ.

What happens when we enter a single quote into the text? Why does this occur?

We mess up the query syntax because the query is wrapped in single quotes. We actually end up closing the query, creating an error because of the extra apostrophe that's left. If the text we enter results in a query that conforms to the SQL syntax, it will be executed. We can exploit this to "inject" our own queries.

How did we determine the number of columns in the COMMODITY table?

We used a UNION and a "select 'A" (leaving out the single quote at the end). This effectively told SQL to union the table where CODE equals nothing, with a string literal 'A'. This failed initially because the columns did not match. We repeated this two more times to determine that COMMODITY has 3 columns.

What is the advantage of zone-based firewalls?

Zone-based firewalls reduce our administrative burden because we do not need to create multiple access lists inbound and outbound traffic

What character caused the error message?

apostrophe '

What is a schema?

Another term for a database