Don't like this style? Click here to change it! blue.css
LOGIN:
Welcome .... Click here to logout
SQL: Standard Query Language
There are (at least) two roles for a modern database:
Running reports from your data
Making user experiences dynamic and interesting
I would argue that
the tension between both of these perspectives is the cause of a great deal of innovation in the
world of software engineering over the course of my lifetime. This tension brought us
REST APIs, ORMs, Active Directory, MonogoDB, GraphQL, Firebase, and probably all of the Javascript Frameworks.
I would say that the way we are using Firebase is entirely about dynamic user experiences,
and the origins of SQL are entirely in running reports. When you go to query firebase for report generation
you will have pain, and when you go to make real-time dynamic user experiences using SQL you will have pain.
SQL is the Spreadsheet of Databases.
Learning SQL
Alright, so how do we get started? I'm going to write down the key ideas here as my narrative roadmap:
SQLite as the simple version
Tables (Columns and/or Relations)
Rows
SELECT what,I,want FROM table(s) WHERE conditionals=filtering
APIs as the data gatekeepers (an access and logic layer)
SQL Injection (SQLi)
The (Old?) Golden Rules of Database Design
JOINs
The Right Mental Model: Binary Relations (oh Discrete Math mattered?)
UNION FTW
sqlite_master and it's cousins
Similarities and Differences between SQLite and MySQL, Postgres, Redis, etc.
Preventing SQLi
Many Flavors and Various Impacts of SQLi
Fuzzing Payloads
There is more to say than I want to make detailed examples for so I'm going to fill in gaps using my voice, be sure to check
out the recorded lecture if you're visiting this page after the fact.
Try to predict what you will see when you run these commands, and once you
have the right mental model make your own commands and predict until you're always right.
Run the query: SELECT * FROM demo WHERE id=1
Run the query: SELECT * FROM demo WHERE id<5
Run the query: SELECT * FROM demo WHERE id<>5
Run the query: SELECT * FROM demo where Hint like "%table_name%";
Run the query: SELECT * FROM demo WHERE id<>5 ORDER BY ID desc
Run the query: SELECT * FROM demo WHERE id<>5 ORDER BY ID desc LIMIT 0, 3
Run the query: SELECT * FROM demo WHERE id<>5 ORDER BY ID desc LIMIT 1, 3
The SELECT clause: Styling the results
Try to predict what you will see when you run these commands, and once you
have the right mental model make your own commands and predict until you're always right.
Run the query: SELECT ID, Name FROM demo
Run the query: SELECT Hint, ID, Name FROM demo
Run the query: SELECT ID as farts, Name FROM demo
Run the query: SELECT 3, 5, 6 FROM demo Note the number of rows
Run the query: SELECT "hi",1337,2 FROM demo WHERE id=1 Note the number of rows
Run the query: SELECT id, Name, "hi" FROM demo WHERE id<=5 Note the number of rows
Aggregating The Data
Run the query: SELECT count(ID) FROM demo
Run the query: SELECT count(Name) FROM demo
Run the query: SELECT count(ID) FROM demo WHERE id <=5
Run the query: SELECT AVG(ID) FROM demo
Run the query: SELECT SUM(ID) FROM demo
Run the query: SELECT SUM(ID) FROM demo WHERE ID <= 5 GROUP BY ID % 5
Run the query: SELECT SUM(ID) FROM demo GROUP BY ID % 5
So the first stage is to go to https://websec.prof.ninja/terrible
and start using it normally. Register an account, make some messages, get a feel.
Then hack at it. Try not to look at source code yet.
If the app gets too monkeyed or there is too much server lag then let me know I can start several or have
you start your own.
You'll need to google around, of course, good luck!
Task 1: Login as AndyNovo without access to his password.
Task 2: Create messages under pseudoAndy's name.
Task 3: Get all usernames and passwords to display on your screen.
Task 4: Find the flag (format is ninja{l33t_sp3a4k_h3r3}).
I'll be giving several micro lectures/hints, and reseting the DB every now and again. DO NOT USE A REAL PASSWORD WHEN REGISTERING.
Micro Lecture/Hint 1: Bypass the login
Hopefully you've figured this one out from Monday's lecture, but when my timer goes off we'll do this together.
Version 1 is as Andy, Version 2 is as pseudoAndy.
Micro Lecture/Hint 2: Union
Once you've got a select statement that you can control which is displaying on screen (or anywhere you can detect)
then you can take advantage of UNION which glues two select statements together.
You must be careful with the number of columns.
Micro Lecture/Hint 3: Master Data
In every database format there will be some kind of master table you can perform a query on to get table names,
column names, and useful detective data. The Sqlite master table is called sqlite_master, google it.
Strategies/Stages
If the attacker has the ability to manipulate queries which are sent to
the database, then they’re able to inject a terminating character too. The
aftermath is that the interpretation of the query will be stopped at the
terminating character:
SELECT body FROM items WHERE id = $ID limit 1;
Let’s assume that the attacker has sent via the GET method the following
data stored in variable $ID:
"1 or 1=1; #"
In the end the final query form is:
SELECT body FROM items WHERE id = 1 or 1=1; # limit 1;
After the # character everything will be discarded by the database
including “limit 1”, so only the last column “body” with all its
records will be received as a query response.
Concept 0: Detecting SQLi
There are a series of "payloads" you can try, and if any of them give a different kind of error than the others, you might be
finding some SQLi. Now a lot of the time the SQLi is in server-side code so you're looking for your distinct errors in the
AJAX response data. But with PHP they are often one and the same which makes it easier to concentrate on the principles.
Here are some of the detection payloads:
Again you need to detect ANY DIFFERENCE between any of these payloads and normal text.
Concept 1: Imagine (or just read) the Source Code
Let's say your intention is to bypass a login. Then imagine (or google) an SQL query used for validating a credential.
That query is probable something like
SELECT count(id) FROM auth_data WHERE username="PARAM1" and credentials="PARAM2"
then if it found any result it'll validate the user as someone it knows.
The essence of the vulnerability is unsanitized input strings being concatenated directly into executed code.
Concept 2: Learn the comment character (Back to normal behavior)
Now that you've got a vague sense of the type of command being run (SELECT or UPDATE with a payload or just a true/false)
we have to be able to NOT cause an error. The key to this is knowing the comment character.
For most SQLs it is -- but for some it is #
also common is // or /* stuff here */ and exotic one is %00
Note that sometimes you can figure out which database vendor they are using and that knowledge helps you craft the right type of query.
In essence if you can 1) GET AN ERROR then add a comment character and now 2) NOT GET AN ERROR then you can start crafting your perfect payload.
Concept 3: Craft your ideal query
Now take a step back from the constraints of your injection point and just imagine what the (or several possible) exact string(s)
you would want SQL to execute could be.
For instance, knowing that I want to bypass an authentication I want a query that returns at least one row. So something like
SELECT stuff FROM users WHERE username="admin"-- could let me login as admin by removing the password/credential check.
Or I could aim for something less specific like SELECT stuff FROM users WHERE username="idontcare" OR 1=1-- so it won't filter anything.
If your goal is to leak information we'll use some more powerful commands that I'll show you after another round of SQL education.
Concept 4: Design your payload to match your ideal
Now you've got a vague idea of where the payload will go, how it must end, and what your target query statement looks like. So now you can
test your guesses/intuition and make the ideal.
So for bypassing auth in our terrible chat you can either know a username or login as the first user:
Now to take your attacks to the next level you'll need to learn a few more SQL concepts.
In SQL database design there is a tendency to have EVERY PIECE OF DATA IN ONE PLACE ONLY.
As a result they will have many tables and the queries must combine them.
Here is a demo database with many tables, let's play with it in a few ways: uni.db
Here are the tools:
.tables
.sqlite_master
pragma table_info(tablename)
SELECT * FROM table1 NATURAL JOIN table2 WHERE filterhere
I'm out of time, so I'll show you UNIONS by hand.
CTF HW
Go to websec.fr and solve problem 1
Master Table Info Across Vendors
In SQLite3 the table with all of the table definitions is
sqlite_master and it has the columns I most care about in name,sql,type where
I want type='table'
In Node the equivalent is similar: connection.query('INSERT INTO users SET ?', post, ...
Under the hood of these solutions is escaping characters in the input text.
In general when you go to write such a thing, you have generally two philosophical directions:
blacklisting characters/phrases that you will prevent
whitelisting the characters/phrases that you will allow
It is surprisingly difficult to implement your own version of either
of those solutions, but to show you why you pretty much have to solve CTF problems
because when you start a good problem you look at it and think, "yeah I could see myself writing that"
then getting afraid because you don't know what the vulnerability is. As you find your way through the
So we've seen it for bypassing login, extracting excess data, and learning the structure of the whole database.
Some other flavors that I've seen:
Stored SQLi (Second-Order SQLi)
This is normally when you store something, in storage, your cookies, their DB, etc. and the actual attack takes place
later when they go to use that data. Same idea but a different set of surfaces you're attacking.
