A Little Noise

July 20, 2007

Complete Set

Filed under: MySQL — snoyes @ 11:40 am

Occasionally in #mysql the question pops up, “How can I get all the values from a table that satisfy all of multiple conditions?”

Then we beat the questioner with a large stick, until they give an example of what they really mean.

SELECT * FROM quizAnswers;
+-------------+----------+
| studentName | question |
+-------------+----------+
| seekwill    | A        |
| seekwill    | B        |
| seekwill    | C        |
| roxlu       | A        |
| fury        | B        |
| fury        | B        |
+-------------+----------+

Find all the students who have answered both questions ‘A’ and ‘B’.
(more…)

July 6, 2007

Views and Social Engineering

Filed under: MySQL Gotchas — snoyes @ 2:07 pm 
CREATE TABLE secretData (
    secretValue int COMMENT 'If this goes over 5, WWIII will start'
);

CREATE SQL SECURITY DEFINER VIEW censoredData AS 
SELECT * FROM secretData WHERE secretValue < 5 WITH CHECK OPTION;

GRANT SELECT, INSERT ON test.censoredData TO 'evilFiend'@'%';

<telephone> ring ring

<sysadmin> "Hello?"

<evilFiend> "I'd like to create an insertable view on some tables I already have rights to. I don't know just yet what I'll use for my select statement."

<sysadmin> "Ok. I'll set it up so you can do what you'd like."

CREATE SQL SECURITY INVOKER VIEW evilFiendsView AS SELECT 1;
GRANT SELECT, INSERT, ALTER ON test.evilFiendsView TO 'evilFiend'@'%';

Evil Fiend

evilFiend connects to the server, while twiddling the end of his handlebar mustache.

ALTER VIEW evilFiendsView AS 
SELECT * FROM censoredData WITH LOCAL CHECK OPTION;

INSERT INTO evilFiendsView VALUES (42);

Muhahaha!

July 5, 2007

Messing with LAST_INSERT_ID()

Filed under: MySQL Gotchas — snoyes @ 10:56 am

Time for another MySQL Community Quiz:

Everybody knows that LAST_INSERT_ID() returns the value most recently created in an auto_increment column. Fewer of us know that you can pass a value to LAST_INSERT_ID(), and that value will be returned for the next call to LAST_INSERT_ID(). For example,

INSERT INTO table (someNonAutoIncrementField) VALUES (LAST_INSERT_ID(42));
SELECT LAST_INSERT_ID();

The INSERT inserts ’42’ into the table, and the SELECT returns 42.

So, here’s the question: What if we pass an expression to LAST_INSERT_ID and also use an auto_increment field in the same statement?

CREATE TABLE test (id int auto_increment primary key, field int);
INSERT INTO test (id, field) VALUES (NULL, LAST_INSERT_ID(42));
SELECT LAST_INSERT_ID();

Show Answer ▼

And for a follow up, does it matter if you swap the insertion around?

INSERT INTO test (field, id) VALUES (LAST_INSERT_ID(42), NULL);
SELECT LAST_INSERT_ID();

Show Answer ▼

The manual warns about mixing LAST_INSERT_ID() and LAST_INSERT_ID(expr). This may fall under that same caveat.

Powered by WordPress