Don't choke on filter expressions that have the password condition on the right hand side of an AND.

Merged
#5 · Created  · Last updated

Merged pull request

Merged in fast-crypts (pull request #5)

aafc771·Author: ·Closed by: ·2014-05-08

Description

Assuming that PlyPlus' lack of backtracking is going to make it impossible to express <expr> and password eq <t_string> as a special branch unambiguously, I've reverted to to treating password as just another token and instead perform the merging of the two AND operands at runtime in the logical_and hook.

Since I don't think I can get access to the non-SQL AST nodes during the transformation phase, I've put in a bit of hack of returning SQL password clauses as different python types so that logical_and knows whether it is dealing with a password condition.

The generated SQL isn't too pretty:

(username eq "evzijst" or emails eq "erik.van.zijst@gmail.com") and password eq "evzijst"

Turns into:

SELECT DISTINCT u.* FROM auth_user u INNER JOIN bb_userprofile p ON p.user_id = u.id LEFT JOIN bb_identity i ON i.profile_id = p.id LEFT JOIN bb_emailvalidationchallenge c ON c.profile_id = p.id WHERE u.id IN ( WITH users AS ( SELECT DISTINCT u.id ,u.password FROM auth_user u INNER JOIN bb_userprofile p ON p.user_id = u.id LEFT JOIN bb_identity i ON i.profile_id = p.id LEFT JOIN bb_emailvalidationchallenge c ON c.profile_id = p.id WHERE u.id IN ( SELECT DISTINCT u.id FROM auth_user u INNER JOIN bb_userprofile p ON p.user_id = u.id LEFT JOIN bb_identity i ON i.profile_id = p.id LEFT JOIN bb_emailvalidationchallenge c ON c.profile_id = p.id WHERE UPPER(u.username) = UPPER('evzijst') UNION SELECT DISTINCT u.id FROM auth_user u INNER JOIN bb_userprofile p ON p.user_id = u.id LEFT JOIN bb_identity i ON i.profile_id = p.id LEFT JOIN bb_emailvalidationchallenge c ON c.profile_id = p.id WHERE UPPER(i.email) = UPPER('erik.van.zijst@gmail.com') ) ) SELECT DISTINCT users.id FROM users WHERE ( CHAR_LENGTH(password) >= 51 AND ( -- Check for SHA1 SUBSTRING(password FROM 12) = ENCODE(DIGEST(SUBSTRING(password FROM 6 FOR 5) || 'evzijst', 'sha1'), 'hex') OR -- Check for BCrypt SUBSTRING(password FROM 8) = CRYPT('evzijst', SUBSTRING(password FROM 8)) ) ) ) ORDER BY u.id ASC

Which has the following query plan:

QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------- Unique (cost=36.51..36.55 rows=1 width=117) (actual time=943.288..943.288 rows=0 loops=1) -> Sort (cost=36.51..36.52 rows=1 width=117) (actual time=943.287..943.287 rows=0 loops=1) Sort Key: u.id, u.username, u.first_name, u.last_name, u.email, u.password, u.is_staff, u.is_active, u.is_superuser, u.last_login, u.date_joined Sort Method: quicksort Memory: 25kB -> Nested Loop Left Join (cost=30.85..36.50 rows=1 width=117) (actual time=943.261..943.261 rows=0 loops=1) -> Nested Loop Left Join (cost=30.85..36.18 rows=1 width=121) (actual time=943.260..943.260 rows=0 loops=1) -> Nested Loop (cost=30.85..35.76 rows=1 width=121) (actual time=943.259..943.259 rows=0 loops=1) -> Nested Loop (cost=30.85..35.27 rows=1 width=121) (actual time=943.259..943.259 rows=0 loops=1) -> HashAggregate (cost=30.85..30.86 rows=1 width=4) (actual time=943.259..943.259 rows=0 loops=1) CTE users -> Nested Loop Left Join (cost=19.44..30.74 rows=2 width=117) (actual time=0.427..0.448 rows=2 loops=1) -> Nested Loop Left Join (cost=19.44..30.10 rows=2 width=121) (actual time=0.422..0.435 rows=2 loops=1) -> Nested Loop (cost=19.44..29.26 rows=2 width=121) (actual time=0.415..0.422 rows=1 loops=1) -> Nested Loop (cost=19.44..28.28 rows=2 width=121) (actual time=0.410..0.412 rows=1 loops=1) -> Unique (cost=19.44..19.45 rows=2 width=4) (actual time=0.405..0.406 rows=1 loops=1) -> Sort (cost=19.44..19.44 rows=2 width=4) (actual time=0.405..0.405 rows=2 loops=1) Sort Key: u.id Sort Method: quicksort Memory: 25kB -> Append (cost=9.67..19.43 rows=2 width=4) (actual time=0.278..0.391 rows=2 loops=1) -> HashAggregate (cost=9.67..9.68 rows=1 width=4) (actual time=0.277..0.277 rows=1 loops=1) -> Nested Loop Left Join (cost=0.00..9.67 rows=1 width=4) (actual time=0.226..0.271 rows=2 loops=1) -> Nested Loop Left Join (cost=0.00..9.35 rows=1 width=8) (actual time=0.205..0.245 rows=2 loops=1) -> Nested Loop (cost=0.00..8.92 rows=1 width=8) (actual time=0.137..0.169 rows=1 loops=1) -> Index Scan using auth_user_username_upper_text on auth_user u (cost=0.00..4.48 rows=1 width=4) (actual time=0.077..0.091 rows=1 loops=1) Index Cond: (upper((username)::text) = 'EVZIJST'::text) -> Index Scan using bb_userprofile_user_id_uniq on bb_userprofile p (cost=0.00..4.43 rows=1 width=8) (actual time=0.052..0.070 rows=1 loops=1) Index Cond: (user_id = u.id) -> Index Only Scan using bb_identity_profile_id on bb_identity i (cost=0.00..0.41 rows=1 width=4) (actual time=0.058..0.066 rows=2 loops=1) Index Cond: (profile_id = p.id) Heap Fetches: 2 -> Index Only Scan using bb_emailvalidationchallenge_profile_id on bb_emailvalidationchallenge c (cost=0.00..0.31 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=2) Index Cond: (profile_id = p.id) Heap Fetches: 0 -> HashAggregate (cost=9.72..9.73 rows=1 width=4) (actual time=0.113..0.113 rows=1 loops=1) -> Nested Loop Left Join (cost=0.00..9.71 rows=1 width=4) (actual time=0.104..0.108 rows=1 loops=1) -> Nested Loop (cost=0.00..9.39 rows=1 width=8) (actual time=0.098..0.101 rows=1 loops=1) -> Nested Loop (cost=0.00..8.93 rows=1 width=8) (actual time=0.061..0.063 rows=1 loops=1) -> Index Scan using bb_identity_email_upper_text on bb_identity i (cost=0.00..4.49 rows=1 width=4) (actual time=0.040..0.040 rows=1 loops=1) Index Cond: (upper((email)::text) = 'ERIK.VAN.ZIJST@GMAIL.COM'::text) -> Index Scan using bb_userprofile_pkey on bb_userprofile p (cost=0.00..4.43 rows=1 width=8) (actual time=0.020..0.022 rows=1 loops=1) Index Cond: (id = i.profile_id) -> Index Only Scan using auth_user_pkey on auth_user u (cost=0.00..0.45 rows=1 width=4) (actual time=0.033..0.033 rows=1 loops=1) Index Cond: (id = p.user_id) Heap Fetches: 1 -> Index Only Scan using bb_emailvalidationchallenge_profile_id on bb_emailvalidationchallenge c (cost=0.00..0.31 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1) Index Cond: (profile_id = p.id) Heap Fetches: 0 -> Index Scan using auth_user_pkey on auth_user u (cost=0.00..4.40 rows=1 width=117) (actual time=0.003..0.003 rows=1 loops=1) Index Cond: (id = u.id) -> Index Scan using bb_userprofile_user_id_uniq on bb_userprofile p (cost=0.00..0.48 rows=1 width=8) (actual time=0.003..0.007 rows=1 loops=1) Index Cond: (user_id = u.id) -> Index Only Scan using bb_identity_profile_id on bb_identity i (cost=0.00..0.41 rows=1 width=4) (actual time=0.004..0.010 rows=2 loops=1) Index Cond: (profile_id = p.id) Heap Fetches: 2 -> Index Only Scan using bb_emailvalidationchallenge_profile_id on bb_emailvalidationchallenge c (cost=0.00..0.31 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=2) Index Cond: (profile_id = p.id) Heap Fetches: 0 -> CTE Scan on users (cost=0.00..0.10 rows=1 width=4) (actual time=943.258..943.258 rows=0 loops=1) Filter: ((char_length((password)::text) >= 51) AND (("substring"((password)::text, 12) = encode(digest(("substring"((password)::text, 6, 5) || 'evzijst'::text), 'sha1'::text), 'hex'::text)) OR ("substring"((password)::text, 8) = crypt('evzijst'::text, "substring"((password)::text, 8))))) Rows Removed by Filter: 2 -> Index Scan using auth_user_pkey on auth_user u (cost=0.00..4.40 rows=1 width=117) (never executed) Index Cond: (id = users.id) -> Index Scan using bb_userprofile_user_id_uniq on bb_userprofile p (cost=0.00..0.48 rows=1 width=8) (never executed) Index Cond: (user_id = u.id) -> Index Only Scan using bb_identity_profile_id on bb_identity i (cost=0.00..0.41 rows=1 width=4) (never executed) Index Cond: (profile_id = p.id) Heap Fetches: 0 -> Index Only Scan using bb_emailvalidationchallenge_profile_id on bb_emailvalidationchallenge c (cost=0.00..0.31 rows=1 width=4) (never executed) Index Cond: (profile_id = p.id) Heap Fetches: 0 Total runtime: 943.780 ms (71 rows)

0 attachments

0 comments

Loading commits...