Access Control Quiz

April 16, 2007

Access Control Quiz

Filed under: MySQL Gotchas — snoyes @ 3:39 pm

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)

4 Comments »

is this a trick question?

SELECT * FROM user ORDER BY 3

Comment by JJ — April 16, 2007 @ 5:47 pm
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.

Comment by snoyes — April 16, 2007 @ 6:42 pm
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);

Comment by Joel — April 16, 2007 @ 9:37 pm
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);

Comment by Michal — April 17, 2007 @ 11:37 am