StackOverflow flashback... Not sure who wrote this comment, and it's a little strongly worded, but man, they're on the money. ;^)

• @samhocevar I can't believe you Little Bobby Tabled me. ;) If you're running text processed by client-side JavaScript on your database server without any safety checks, heaven help us all. ;^) Look, there shouldn't be anything any user can send from a client (eg, Postman) that gets past your server's security. And you should assume anything dangerous that could be sent from a client will be. That is, if you require 100% safety from client-side JavaScript code which is always user editable, and you think this function could open a security risk, you're playing in the wrong game. – ruffin Feb 19 '18 at 18:57  

I hope that makes sense. Sure, it's not bad to include some safety checks client-side, but they really are just niceties. Maybe you check a few things for your user's user input without a round trip to the server, but only after you've already put those same rules into your server's code.

(This, btw, is why Node is so intriguing. Write once with both sides in mind and you get client-side niceties for "free". Otherwise, if you push me to shoving, I'd say an mvp should only have input validation on the server [in ways that make it easy to return error packages to the client to display]. (And thus the * in the title... you can have it on the client, but it's not worth anything from the point of view of security.))

Honestly, this dev taking me to task by saying people can get formatUnicorn (read: client-side JavaScript code) to inject strings into (again) client-side variables... I mean, I can't even. WHY NOT JUST INPUT THE WACKY STRING TO BEGIN WITH? And to think that anything in JavaScript protects your server... Ugh. So many bad programmers loose these days. I'd like to think they're junior, but imagine that's a senior dev that forces juniors to protect against string injection in client code, maybe client code only. /shiver

Labels: development, security, software, SQL, stackoverflow

So here I am, happily streaming songs from Prime via Amazon Music, when I hear a song from Garbage I know (#1 Crush*). I know this isn't in my favorites playlist on iTunes. Why not?

I check, and sure enough, it's a B-side, and I haven't shelled out for the Garbage 20th Anniversary Super Deluxe edition. ;^) So let's see if I can just buy the single file to "add" to my plain wayne version of Garbage (the album).

Search a little, and...

Nothing. I tried a few other options, and still nothing. Lots of crush, not so much crush from Garbage. That's interesting.

Surely (Shirley?) they have the song, right?

Yes they do. And now I'm previewing it. (See it, down there at #18?)

This is as bad as the "Big Jennifer Null" problem I blogged about a while back. THIS IS NOT LITTLE BOBBY TABLES. It'd be bad enough if it was, as even "real" SQL injection should be caught by anyone worth their salt these days. But not being able to search for special characters? No, past that, not being to able to find a title that contains special characters? That's just sad. Unforgivably sad.

It's not difficult to search for a string that begins with "#", dang it. Grow up, Apple. I'm dying to fix this. Please let me fix this. As I said before about J-Null, this isn't Little Bobby Tables. This is stoooopid.

Insult to injury? When I started writing this, iTunes decided it'd take 23.7% of my CPU. I'd noticed Garbage had a new release, and was on that page, doing nothing. Not playing any music on a page without even a rotating photo carousel (thanks a lot, Draper) takes 23.7% of my CPU.

Fail, Apple. That's a huge fail.

* Ha, I can't even make an affiliate link to #1 Crush.

Pro tip: Do not search for "Hooper Mix" on the affiliate link tool for the iTunes Music Store. Borderline NSFW. Thanks again, Apple.

Labels: apple fail, coding, fail, iTunes, SQL

My new favorite quote of Marc Gravell's from StackOverflow:

based on the SQL, all I can say is: both queries look horrible. I knew there was a reason I normally write my own TSQL... – Marc Gravell♦ Jun 15 '12 at 7:29

I understand having entities for editing and manipulation, but don't know why every read that's done a system that employs a conventional rdbms isn't done with native SQL.

Labels: entity framework, orm, SQL, stackoverflow

Caught this gem from the BBC via Michael Tsai's blog:

“We moved almost immediately after we got married so it came up practically as soon as I changed my name, buying plane tickets,” she says. When Jennifer Null tries to buy a plane ticket, she gets an error message on most websites. The site will say she has left the surname field blank and ask her to try again.

Instead, she has to call the airline company by phone to book a ticket – but that’s not the end of the process.

“I've been asked why I'm calling and when I try to explain the situation, I've been told, ‘there's no way that's true’,” she says.

Sorry to say I pulled a legitimate LOL as soon as I read her last name. Ouch.

But my reaction? Gosh, which websites and what horrible programmers? I mean, even if you're a two-equals coder (you should use three, natch) in JavaScript, the weakest place I could think of off-hand, null != "null", and you've got no problem.

And...

INSERT INTO Users (Name) VALUES ('Null') -- (as the result of a parameterized query)

... screwed up nobody ever. Which leads me to say...

This is not Little Bobby Tables.

This is stoopid.

We're all stoopid sometimes, but that's what this is. It's stoopid.

???

After a little more digging...

Seems I ran through the same steps in my head as the SO post the BBC includes, but the answer seemed obvious; this is the "worst case" I could think of going in...

The only reasonable workaround I can think of, short of fixing this bug in every damn version of ActionScript, is to test fields for "null" and escape them as CDATA values.

Well, duh. Though extend my sympathy for those who have a legacy system that still uses XML, not JSON. ;^)

What this really shows is...

• There's waaaaaaay too much NIH syndrome in the enterprise
• We're exceptionally bad at creating test cases
• We need more imaginative coders.

If the name "Null" is still an issue three to six months from now, SHAME. No, ALL CAPS SHAME. FOR REAL. Horrible.

Wow. Seriously, I'm embarrassed for the profession. At worst, you needed to see why your client-side was "letting through" names that were empty so far that they got serialized to XML. Does nobody check the logs? Or are your logs so chatty you wouldn't see this error?

/facepalm

Labels: coding, fail, SQL

I've got an MVC app using Entity Framework that has some complex relationships in a notifications module. I've got different queries to get parameters related to existing vs. new subscriptions to events that potentially produce notifications.

For the existing subscriptions, I created a view, and then did a SQL to EF to create an entity based on that view. Beautiful, straightforward, no problems.

For the new ones, just for fun, I decided that I'd do it all in EF. What a freaking mess. Here's the flowerbox from the repository method, reformatted a touch for width:

// NOTE: I wanted to see how difficult this would be outside of creating a
// view like I did for GetParameterSubscriptionMatrixesBySubscriptionId. I
// think the lesson is that creating a view is smarter.
//
// "LINQ LEFT JOIN on Nullable<int>"
// http://stackoverflow.com/a/28949184/1028230
//
// Populating an entity from a custom join
// http://stackoverflow.com/questions/5325797/
//
// And, tangentially, Overriding Equals and GetHashCode for entity equality.
// http://stackoverflow.com/a/508157/1028230 

That's right, three SO questions plus an insanely nasty query, where I'm still, admittedly, embarrassingly, also appending a Distinct on the collection before sending the entities back as a List. There's some strange left joining going on as a result of the workaround to allo a join on a nullable int that I haven't been able to quickly remove.

Let's list those SO questions again, just to linkify them more easily.

• "LINQ LEFT JOIN on Nullable<int>"
• Populating an entity from a custom join
• Overriding Equals and GetHashCode for entity equality.

Nasty. EF is broken. It's enough different from SQL that it's a new, often inferior, (& never, that I recall, superior) paradigm. Better not to support this, and to force you to use SQL for these tasks.

Do you hire for SQL competency? Then put as much logic as you can in your code's database.

Labels: ef, orm, SQL

One thing I've constantly wondered about with ORMs is why they use different mental models than SQL, the thing they're almost always abstracting. We hire for SQL proficiency, but then immediately throw it away with complex edge case stuff. You know, like LEFT OUTER JOINs. /sarcasm

From MSDN:

The first step in producing a left outer join of two collections is to perform an inner join by using a group join....

The second step is to include each element of the first (left) collection in the result set even if that element has no matches in the right collection. This is accomplished by calling DefaultIfEmpty on each sequence of matching elements from the group join.

Yes, you heard that right. To perform a left outer join, you perform an inner join into a join table, then fill that join table in with default (null?) values if there's no inner join match.

Wth? Their example:

var query = from person in people
    join pet in pets on person equals pet.Owner into gj
    from subpet in gj.DefaultIfEmpty()
    select new { 
        person.FirstName, 
        PetName = (subpet == null ? String.Empty : subpet.Name) 
    };

I'm tempted just to write a view and use that instead. This is stupid. The syntax really should be...

var query = from person in people
    left outer join pet in pets 
        on person equals pet.Owner
    select new { 
        person.FirstName, 
        PetName = subpet.Name ?? string.Empty
    };

I don't get it. I wonder how hard it'd be to write an extension that does that...

Labels: ef, noteToSelf, SQL, SQL Server

type char(2)
 

Object type:
AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SO = Sequence object 

Applies to: SQL Server 2012 through SQL Server 2016.
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure

IF OBJECT_ID('TableName', 'U') IS NOT NULL
DROP TABLE TableName;

Labels: noteToSelf, SQL, SQL Server, t-sql

Why should you use DateTimeUTC and not DateTime in your SaaS systems? 4guysFromRolla actually has a pretty good answer from 2007:

The primary advantage of storing date/time values in UTC is that it makes the data transportable. To see what I mean, imagine that following scenario: you have an eCommerce website that is being hosted in a web server located in the Pacific time zone (UTC -8) and this application stores the date and time orders were placed in server time. Say a user, Bob, makes an order on August 1, 2007 at 9:00 AM UTC -8. After many months of phenomenal growth, you decide to switch to a larger web hosting company, one on the east coast where the time zone is UTC -5. Since the date/time is stored in server time, Bob's previous order still shows that it was made on August 1 2007 at 9:00 AM. But since we are now in UTC -5, it is as if Bob's order was made three hours earlier than it really was (since when it was 9:00 AM on August 1, 2007 in the west coast it was really 12:00 noon on the east coast).

One way around this, you might contend, is to execute a SQL query that adds three hours to the order date for all records in the table. Something like:

UPDATE Orders SET

OrderDate = DATEADD(hh, 3, OrderDate)

And such an approach would suffice... for this situation. But imagine that you moved to a web hosting company situated in the US state of Arizona, where daylight savings is not observed. E[s]p. Now you would have to write a more complex UPDATE statement that adjusted the hours based on whether the order date fell within daylight savings. Ick.

...

Things like daylight savings further complicate working with dates and times, but UTC does not observe any time zone, simplifying things a tiny bit. [emph mine -mfn]

To make a long story short, you have two choices:

• Use DataTimeUTC and use built-in converters to get "local" time, usually on the client
• Pick another "native" time zone for your system, and prepare to maintain your own converters

Seems an easy enough choice to me.

Labels: .NET, coding, dates, SQL

I've got a project where I've been "given"^[1] code that has lots of display logic in sprocs on SQL Server, and that display logic (colors, in this case) is fairly inextricably tied in with the data I want to use too. That is, we have several tables that hold raw data, and these sprocs both tease out the data and put it, somewhat inextensibly, into what boils down to one giant "row" of data. In other words, the data in the sproc's output is not normalized.

That info is, in the original system, passed on to SSRS (SQL Server Reporting Services, a, afaict, sort of SQL Server specific Crystal Reports equivalency) and translated, somewhat painfully, into SSRS's pseudo-html.

You can get those results into another table pretty easily, via building a table whose structure matches the output and using something like insert into spam EXEC crazySproc @p1 = 'crazy', @p2 = 'sproc'. And then you're off to the races. Sort of.

Because of the way we've got this set up, all the values are in a single row that extends until the end of time, with specialized display column value after specialized display column value interspersed with the raw data we want to operate on.^[2]

The Right Thing To Do would be to rewrite the sproc to give us normalized data. But The Quick Thing would be to try and get each column name and value along with that value's display info into a single row we can easily JSON up and send, packaged with a little more display info that'll replace the static setup we have in SSRS, to the client.

So from this:

+------+--------+---------+-----------+-----------+--------+
| val1 | val2   | ...valn | colorVal1 | colorVal2 | ...etc |
+------+--------+---------+-----------+-----------+--------+
| 5    | 8.23   | ...     | red       | green     | ...etc |
+------+--------+---------+-----------+-----------+--------+

We want to have...

+------------+--------------+-------------+---------+--------------------------+-----------+
| metricName | metricAmount | metricColor | ordinal | label                    | isPercent |
+------------+--------------+-------------+---------+--------------------------+-----------+
| val1       |            5 |         red |       1 | This is the first value  |         0 |
| val2       |        8.23  |       green |       2 | This is the second value |         1 |
| ...        |              |             |         |                          |           |    
+------------+--------------+-------------+---------+--------------------------+-----------+

We can join on metricName pretty easily to get those extra fields on the right, though, as always, I hate joining on character strings. But desperate times and all that; we're joining to a sproc result that only expected to be used for one purpose, and didn't anticipate reuse.

So how to turn the first table into one that can be used to build the second? UNPIVOT it, turning columns into rows.

Generic case

I'm not sure why, but I had to stare at some examples for a while before I kinda got what was going on with UNPIVOT. Let's just go ahead and look at an extended but simplistic example:

create table #unpivotMe
(
    id int identity(1,1) not null,
    col1 varchar(50),
    col2 varchar(50),
    col3 varchar(50)
);
go

insert into #unpivotMe (col1, col2, col3)
values
    ('1v1', '1v2', '1v3'),
    ('2v1', '2v2', '2v3'),
    ('3v1', '3v2', '3v3');

select * from #unpivotMe

So far, so simple enough...

+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
| 1  |  1v1 |  1v2 |  1v3 |
| 2  |  2v1 |  2v2 |  2v3 |
| 3  |  3v1 |  3v2 |  3v3 |
+----+------+------+------+

Here's how you need (or how I needed) to think of what comes next -- Each column is a name and value pair. And we have a choice with each column. We can:

1. ignore it,
2. translate it into a single value in a row,
3. ... or display it "as-is", without UNPIVOTing.

Let's start by unpivoting every coln column (option 2 across the board), turning each named column into a row value.

select * from #unpivotMe
unpivot
(
    colValue for col in (col1, col2, col3)
) as unpivoted

The result is reasonably neat. Remember that, in my use case, there are no ids and only one row, so it'd stop after the third row displayed below.

+----+----------+------+
| id | colValue | col  |
+----+----------+------+
| 1  |      1v1 | col1 |
| 1  |      1v2 | col2 |
| 1  |      1v3 | col3 |
| 2  |      2v1 | col1 |
| 2  |      2v2 | col2 |
| 2  |      2v3 | col3 |
| 3  |      3v1 | col1 |
| 3  |      3v2 | col2 |
| 3  |      3v3 | col3 |
+----+----------+------+

Just for fun, let's experiment with option 3, "display a column as is", by leaving col3 out of the unpivoting this time, but keeping it in the SELECT clause.

select * from #unpivotMe
unpivot
(
    colValue for col in (col1, col2)
) as unpivoted

Fewer rows, and an extra column.

+----+------+----------+------+
| id | col3 | colValue | col  |
+----+------+----------+------+
|  1 |  1v3 |      1v1 | col1 |
|  1 |  1v3 |      1v2 | col2 |
|  2 |  2v3 |      2v1 | col1 |
|  2 |  2v3 |      2v2 | col2 |
|  3 |  3v3 |      3v1 | col1 |
|  3 |  3v3 |      3v2 | col2 |
+----+------+----------+------+

This shows us that what's explicitly listed in the unpivot clause are the only columns that we're going to "explode" into row values, which allows some interesting uses.

There are some crazy caveats, however, like that the cols in the UNPIVOT have to be the same type and length apparently. Check out this link at "If we attempt to use the UNPIVOT operator to do this, we run into trouble". That's pretty painful, and requires some wacky casting to keep up the shortcut charade, below.

One row mash-up use case (kludges ahoy!)

Let's also create something closer to my original use case, so you can tell exactly how it's useful.

create table #valuesAndDisplayInfo
(
    val1 decimal(5,2),
    val2 decimal(5,2),
    val3 decimal(5,2),
    colorVal1 varchar(15),
    colorVal2 varchar(15),
    colorVal3 varchar(15)
)

insert into #valuesAndDisplayInfo
    values
    (5, 8.23, 98.85, 'red', 'green', 'yellow');

select * from #valuesAndDisplayInfo;

That gives us the giant single row of data that parallels what the sproc I talked about gives me.

+------+------+-------+-----------+-----------+-----------+
| val1 | val2 | val3  | colorVal1 | colorVal2 | colorVal3 |
+------+------+-------+-----------+-----------+-----------+
| 5.00 | 8.23 | 98.85 |       red |     green |    yellow |
+------+------+-------+-----------+-----------+-----------+

That's kind of nasty. There are three values with three colors for display, all in the same place. Am I supposed to just JSON that up and look for every label in the format val[0-9]*? Yuck.

So let's UNPIVOT.

select * from #valuesAndDisplayInfo
unpivot
(
    value for field in (val1, val2, val3, colorVal1, colorVal2, colorVal3)
) as valuesAsRows

Whoops!

The type of column "colorVal1" conflicts with the type of other columns specified in the UNPIVOT list.

Remember that we have to have all of the column types the same in our UNPIVOT list. Let's get cast crazy.

select field, value from (
    select
        cast(val1 as varchar(15)) as val1,
        cast(val2 as varchar(15)) as val2,
        cast(val3 as varchar(15)) as val3,
        colorVal1,
        colorVal2,
        colorVal3
    from #valuesAndDisplayInfo
) as castedValAndInfo
unpivot
(
    value for field in (val1, val2, val3, colorVal1, colorVal2, colorVal3)
) as valuesAsRows

Though the casting stinks, that's not puke out loud horrible, but I really wanted the colors to be on the same row as the raw values. Right now, they aren't.

+-----------+--------+
| field     | value  |
+-----------+--------+
| val1      | 5.00   |
| val2      | 8.23   |
| val3      | 98.85  |
| colorVal1 | red    |
| colorVal2 | green  |
| colorVal3 | yellow |
+-----------+--------+

Here's the bullheaded, inefficient, magic string way around that that we probably oughta integrate earlier.

-- Note that we're selecting into a "real" table now
-- Probably should use a temp table.
select field, value into unpivotedVals from (
    select
    cast(val1 as varchar(15)) as val1,
    cast(val2 as varchar(15)) as val2,
    cast(val3 as varchar(15)) as val3,
    colorVal1,
    colorVal2,
    colorVal3
    from #valuesAndDisplayInfo
) as castedValAndInfo
unpivot
(
    value for field in (val1, val2, val3, colorVal1, colorVal2, colorVal3)
) as valuesAsRows

select v1.field, v1.value, v2.value as color from unpivotedVals v1
    inner join unpivotedVals v2
        on v2.field = 'color' + v1.field

Success. I don't love it, but you can see how unpivoting helped us get here.

+-------+-------+--------+
| field | value | color  |
+-------+-------+--------+
| val1  |  5.00 |    red |
| val2  |  8.23 |  green |
| val3  | 98.85 | yellow |
+-------+-------+--------+

Pretty good introduction and further exploration of UNPIVOT at those links, which is also where I found out about the type and length restrictions for UNPIVOT. I haven't read all the way through, but it looks like he has a method of using APPLY that might make this less painful.

Again, The Right Way is to rewrite the sproc, which I think I'm going to do, but this is a nice an incredibly kludgey, defensive (as in it doesn't affect any of the "given" code) method to get from A to B.

[1] Hrm, not exactly "given". Inherited? What's it called when you have contractors who are hired before you start and pick a stack that's probably not what you would've picked?

[2] "... on which we wish to operate"?

Labels: SQL

Getting the text of existing sprocs is apparently pretty easy: EXEC sp_helptext N'sp_get_composite_job_info';

Voila.

I've been having trouble with ordering the results of a stored procedure, probably by putting results in a temp table. Seeing, in this case, the code to create the temp table the sproc's giving back should be useful.

Although, in my case, no dice. I ended up cheating and trivially rewriting the sproc and the sproc it called.

sp_help_job calls sp_get_composite_job_info, which ends with a statement with its own ORDER BY, which is all I was interesting in changing.

So a quick change there...

-- ...
FROM @filtered_jobs fj
LEFT OUTER JOIN msdb.dbo.sysjobs_view sjv ON (fj.job_id = sjv.job_id)
LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON (sjv.notify_email_operator_id = so1.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON (sjv.notify_netsend_operator_id = so2.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON (sjv.notify_page_operator_id = so3.id)
LEFT OUTER JOIN msdb.dbo.syscategories sc ON (sjv.category_id = sc.category_id)
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--ORDER BY sjv.job_id
ORDER BY name
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

... and a quick change in `sp_help_job`...

-- Generate results set...
--EXECUTE sp_get_composite_job_info @job_id,
EXECUTE WACK_sp_get_composite_job_info @job_id,
    @job_type,
    @owner_login_name,
    @subsystem,
    @category_id,
    @enabled,
    @execution_status,
    @date_comparator,
    @date_created,
    @date_last_modified,
    @description

... and I'm working. (Or I could have just overwritten spgetcompositejobinfo with one that sorted different, but that's obviously destructive, and usually A Very Bad Idea.)

Labels: sproc, SQL, SQL Server

You can also use data to search for your suggestions that were rejected or approved. I have just created two queries for this:

* My rejected edits
* My accepted edits

Labels: SQL, stackoverflow

Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.

Labels: SQL

1 Open phpMyadmin.
2 Select a database to work with.
3 Open the SQL tab.
4 Select all of the SQL statements between the DELIMITER statements in your stored procedure script. Do not include the DELIMITER statements!

Here’s what my example script should look like:

DROP PROCEDURE IF EXISTS spFoo $$

CREATE PROCEDURE spFoo ()

BEGIN

	SELECT 'Foo' FROM DUAL;

END $$

5 In the delimiter field, just below the SQL editor’s text area, enter $$ as your delimiter.

Labels: phpMyAdmin, problem solved, SQL

I see many people use subqueries or else vendor-specific features to do this, but I often do this kind of query without subqueries in the following way. It uses plain, standard SQL so it should work in any brand of RDBMS.

SELECT t1.*
FROM mytable AS t1
     LEFT OUTER JOIN mytable AS t2
          ON (t1.UserId = t2.UserId AND t1.'Date' < t2.'Date')
WHERE t2.UserId IS NULL

Labels: code, problem solved, SQL