Presented without comment:
|
1 2 3 4 |
CREATE TABLE dbo.[FROM] ([SELECT] INT, [WHERE] INT, [LIKE] INT); GO SELECT [SELECT] FROM [FROM] WHERE [WHERE] LIKE [LIKE]; GO |
Next up, can you break up a query with spaces? Yep:
|
1 |
SELECT * FROM sys . databases; |
Well if you can do that – can you break up a query across lines? Sure you can:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT * FROM sys . databases WHERE name = 'tempdb' ; |
And now, brace yourself: this one is so weird that I can’t even embed it in the blog. I’m just going to show you a picture of it first:
You’re probably going to go through a few phases as you look carefully at that code:
- “Is it going to work?”
- “No – that can’t possibly work.”
- (Then you’re going to copy/paste the code from a Github gist and try it, but here’s the key: don’t try to edit it yet, just run it.)
- “How did that work?”
- “Why doesn’t the proc return any results?”
And then you’re going to try editing it, and things are going to get really weird. I wish I had a webcam to see your face as you worked through it. I’ll follow up with the secret in a later post. Here’s your only clue: Solomon Rutzky knew what it was as soon as he saw the code.
33 Comments. Leave new
Similar idea as : “Revenge: The SQL!”
https://sqlbits.com/Sessions/Event12/Revenge_The_SQL
I put a “hex” on you.
You missed no spaces at all 😀
SELECT[SELECT]FROM[FROM]WHERE[WHERE]LIKE[LIKE];
https://sqlstudies.com/2017/04/13/no-space-queries/
On the last one, I’d hazard to guess that the names of the similar looking columns/parameters are comprised of similar looking unicode characters.
CTRL+F “?????” That’s my hint.
Great use-case for quoted identifier!
I’ve always wondered why you are allowed to have a trailing comma within the parenthesis of a CREATE TABLE statement. Not a “trick” per se, but odd. Also, don’t forget about single-character variables and local temporary tables. For example (including a bonus trick):
— DROP TABLE #;
CREATE TABLE # (? INT, );
DECLARE @ INT;
SELECT ? FROM # WHERE ?= @;
I’m not sure if the above with copy/paste correctly, or even encode/decode properly, but we shall see in a moment 😉
Well, it looks like that did not encode properly. There was not supposed to be a “?” in that example. This page claims to be encoded as UTF-8, so I guess that character got lost somewhere on the back-end. If this next attempt works, then I think I can post it correctly. If not, then maybe I will just comment on the Gist. Either way, happy cat: 😺 (or at least attempted happy cat 😉 )
Hey, that did work. So, final attempt:
CREATE TABLE # (ᅟ INT, );
DECLARE @ INT;
SELECT ᅟ FROM # WHERE ᅟ= @;
Fun with ASCII eh?
https://frinkiac.com/meme/S07E21/593475/m/QW5kIHlvdSBjYWxsIGl0CiJmdW4gd2l0aCBhc2NpaSIKZGVzcGl0ZSB0aGUgZmFjdCB0aGF0Cml0IGlzIG9idmlvdXNseSB1bmljb2Rl
It’s not often my past experience as an assembler programmer comes to the rescue. UTF8 bytestring to the rescue! I learned something today.
Robert – heh heh heh, good call.
I recommend materials from the session of Marek Adamczuk from the sqlday2018 conference.
I liked the sql inside the function.
https://github.com/SQLDay/2018/blob/master/Marek%20Adamczuk/sqlday2018.zip
Sample:
create or alter function dbo.arith_add (@arg1 float = 0, @arg2 float = 0)
returns float
as
begin
return (@arg1 + @arg2);
end;
GO
create or alter function dbo.arith(@operation nvarchar(20),@arg1 float = 0, @arg2 float = 0)
returns float
as
begin
declare @fnname sysname, @result float;
select @fnname = ‘dbo.arith_’+@operation;
exec @result = @fnname @arg1, @arg2; –WHAT???
return @result;
end;
GO
select
dbo.arith(‘add’,6,2) as [add] –8=6+2
Hi there. That is creative, but to be clear, it is not Dynamic SQL. EXEC does let you specify the module to execute via a variable (just the module, no other T-SQL), and you can execute scalar UDFs (in which case the UDF can actually have optional parameters, like procs, and you can ignore the return value). You can, however, do real Dynamic SQL in a scalar UDF using SQLCLR (since all SQL submitted via SQLCLR objects is Dynamic SQL).
SMSS, Ctrl+F and copying one of the .oOo. into the search field quickly (well, almost quickly) lead to the same conclusion as others before me, saving and looking at it in a hex editor confirmed it, but then I noticed something really strange for which I found no explanation yet.
On github, at least when I look at it here in Chrome, there’s an @ and a [ that swapped positions in line 23. When I copy the entire block of code and paste it into SMSS or Notepad, they magically jump back in place.
BTW, line 23 at github is the WHERE line, not the same as here.
Luc – hee hee! Yep, now you’re starting to discover another layer of fun in the code. That part is my favorite.
hmmm – this is beyond me. “And then you’re going to try editing it, and things are going to get really weird. ” . What would you like to change? EXEC [?????].[?????].[?????]
@????? = N'[1]’,
@????? = N'[2]’,
@????? = N'[3]’,
@????? = N'[4]’
GO gives the same result, what i expected. So there must be something weird going on what everyone notices, but i don’t. I don’t understand how these columns got the same name, but… what weirdness should we encounter when editing?
Willem – try moving things around inside the code.
Quite amusing. Surely the columns in a table can’t have same names so those funky characters can’t be identical. Here’s how to compare them:
USE [?????]
GO
SELECT
NAME AS “Column Name”,
LEFT(NAME,1) AS “Left Char”,
UNICODE(LEFT(name,1)) AS “Left Char Code”,
RIGHT(NAME,1) AS “Right Char”,
UNICODE(RIGHT(name,1)) AS “Right Char Code”
FROM sys.all_columns WHERE object_id=OBJECT_ID(N’?????.?????.?????’)
Just replace ????? with the database name, take that .oOo. from the original code.
this works too: SELECT*FROM sys . databases;
That really made us laugh!
A little more…
CREATE TABLE dbo.[FROM] ([SELECT] INT, [WHERE] INT, [LIKE] INT, [ON] INT);
GO
CREATE TABLE dbo.[JOIN] ([=] INT, [AND] INT, [] INT, [&] INT, [OR] INT);
GO
SELECT [SELECT] FROM [FROM] JOIN [JOIN] ON [ON] = [=] WHERE [WHERE] LIKE [LIKE] AND [AND] < [ [>];
GO
That didn’t appear as planned, there are greater-than, less-than and ampersand columns being used against themselves – you get the idea.
My hint is to try combination of MDCLXII, MDCCLXXVI
One thing i don’t know yet: flipping of brackets and other after treating code as literal.
Hmm.. is it due to Arabic is right to left? 🙂
Yeah but it seems that this behavior of SSMS editor is only for pair of not extended arabic characters.
????? isn’t equal to ?????
Oddly enough, select*from yourTable doesn’t cause a parsing error. Something with * here doesn’t require a space.
Brent, i do not know if you have seen my comment on github , i suggest this to make it funnier :
CREATE TYPE [?????] from nvarchar(20)
SO you can avoid to refer to nvarchar(20) and can use [?????] as datatype
CREATE TABLE [?????].[?????]([?????] [?????], [?????] [?????], [?????] [?????], [?????] [?????]);
and the same in CREATE PROC
😀
Hahaha, nice.
Now my brain hurts.
Decades ago, there was a programming language pushed by IBM called PL/1. It was a mutant pastor child of COBOL, Fortran, and a little alcohol ..er Algol. It was going to be the super language that replaced all of them in particular COBOL. People inside IBM actually bragged that it took three times as much code is the same COBOL program so you have to buy more hardware
.
One of the language properties was that you didn’t have reserved words. The syntax is defined in VDL (Viennese data language) so that you could write an incredibly complicated parser. It took three passes and it would figure out what was what. Oh the three passes were fun; left to right, doing some weird tokenization, right to left to take advantage of these strange special tokens and correct them, and finally 1/3 pass left to right to get you something that could be executed
.
I was taking a programming language course at IIUPUI (Indiana University – Purdue University at Indianapolis) while working in Indianapolis and we had a student PL/1 compiler . We had one student in the class who was absolutely fascinated with the equivalent of what you’re doing your first example with Microsoft proprietary square brackets, namely putting reserved words in is variable names. He loved to declare DECLARE as the variable name. For instance; but in PL/1. You didn’t have to put brackets or quotes or anything around it; the compiler was supposed to figure that out by itself! He was also fascinated by the fact that you did one statement per punchcard (yes, this was so long ago we used punchcards). All of his programs were monolithic blocks of characters. They look like a bad core dump. Very quickly. The student got the nickname “mad bomber” with the staff was running our punchcards.
Another feature of this super language was that you could have coroutines – proc A calls Proc B, then Proc B calls proc A, etc . You probably know this as the “producer consumer model” of programs. It used to be very exotic. The mad bomber decided to write a ring of A => B => C => A coroutines.
Because the staff knew his programs would fail, they always moved his deck of punchcards to the end of the queue. This “circle jerk” was run as the last job on Friday night before everyone went home for the weekend. The staff expected the bombers program to have failed and they would get to it on Monday.
The student compiler could only handle pairs of such things. It allocated a dope vector in memory for the incarnations of a routine. This three-way “circle jerk” led to vectors being endlessly allocated. First, it filled up actual memory, then it filled up virtual memory, and it finally hung some sort of weird, infinite loop. The IBM repair guy removed the core from the machine to take it back to the lab so they could see exactly what it happened, as it was beginning to burn out physical memory.