A Little Noise

Access Control Quiz

Written by

snoyes

in

First, the setup:

CREATE TABLE `user` (
  `user` varchar(255) DEFAULT NULL,
  `host` varchar(255) DEFAULT NULL,
  `sort` int(11) DEFAULT NULL
);

INSERT INTO `user` 
    (`user`, `host`, `sort`) 
VALUES 
    ('','%',8),
    ('testUser','%',7),
    ('','%localhost',9),
    ('testUser','%localhost',5),
    ('','%localhost%',10),
    ('testUser','%localhost%',6),
    ('','localhost',2),
    ('testUser','localhost',1),
    ('','localhost%',4),
    ('testUser','localhost%',3);

Now, the quiz:

SELECT * FROM user ORDER BY ___

Fill in the blank to get the following output. Difficulty: the `sort` field may not appear anywhere in the query.

+----------+-------------+------+
| user     | host        | sort |
+----------+-------------+------+
| testUser | localhost   |    1 |
|          | localhost   |    2 |
| testUser | localhost%  |    3 |
|          | localhost%  |    4 |
| testUser | %localhost  |    5 |
| testUser | %localhost% |    6 |
| testUser | %           |    7 |
|          | %           |    8 |
|          | %localhost  |    9 |
|          | %localhost% |   10 |
+----------+-------------+------+

Comments

4 responses to “Access Control Quiz”

  1. JJ Avatar
    JJ

    is this a trick question?

    SELECT * FROM user ORDER BY 3

  2. snoyes Avatar
    snoyes

    That’s still using the field, even though it doesn’t use the word “sort”. For the quiz, just use that sort field as a check to know you got it right.

  3. Joel Avatar
    Joel

    I doubt this is the most elegant solution, but here is a working one. If you are looking for a solution using ordering by conditionals, is there a less complex ordering method?

    SELECT * FROM user
    ORDER BY
    IF(POSITION(‘%’ IN host) = 1, 1, 0),
    IF(POSITION(‘%’ IN host) = 10, 1, 0),
    IF(user != ‘testUser’, LENGTH(host), 0),
    IF(host != ‘%’, LENGTH(host), 11);

  4. Michal Avatar
    Michal

    I guess this one would be more accurate to find correct host, but doesn’t work as you’d expected ;)

    select * from user
    order by
    if(locate(‘%’, host) = 0, 0, IF(locate(‘%’, host) = 1, 2, 1)),
    user desc,
    if(substr(host, -1) = ‘%’, IF(host = ‘%’, 2, 1), 0);

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More posts