Fun with single quotes

Speakerphone_Dude

@blakeyrat said:
@morbiuswilters said:
An aside, since I respect your opinion: can you explain the benefit of using stored procedures for every single database access? I've worked with a couple of people who insist on doing this and it drives me crazy. When I pressed them for a reason, they could never provide one other than "It makes things cleaner". Cleaner how? Why are they doing this?

I do that so it insulates the schema from the application. It's easier to keep the database flexible if you don't have to make app changes at the same time you make schema changes, and sprocs allow that easily.

I guess I can see how that might be desirable, but I think that MMMV. I find that there are a lot more changes to the queries than accounted for by schema changes. We do have configuration control over DDL updates, but it's a lot easier to deal with queries in the code. Plus, when you're looking at the code, the query is right there, which makes debugging a lot easier.

Two more benefits of stored procedures:

You can maintain stored procedures as individual artifacts (such as using Visual Studio Database Projects), which opens the door to database unit tests
Stored procedures allow the server to reuse execution plans, which has a positive impact on performance

blakeyrat

@boomzilla said:

I guess I can see how that might be desirable, but I think that MMMV. I find that there are a lot more changes to the queries than accounted for by schema changes. We do have configuration control over DDL updates, but it's a lot easier to deal with queries in the code. Plus, when you're looking at the code, the query is right there, which makes debugging a lot easier.

You can debug your sproc without even leaving the database environment. Or do you work in some crazy place where you can create sprocs, but can't execute them?

boomzilla

@blakeyrat said:

@boomzilla said:
I guess I can see how that might be desirable, but I think that MMMV. I find that there are a lot more changes to the queries than accounted for by schema changes. We do have configuration control over DDL updates, but it's a lot easier to deal with queries in the code. Plus, when you're looking at the code, the query is right there, which makes debugging a lot easier.

You can debug your sproc without even leaving the database environment. Or do you work in some crazy place where you can create sprocs, but can't execute them?

No, nothing like that. I have my own schema where I can pretty much do anything I want. But I can look at code and queries (where it isn't in a sproc) all in the same place, because the queries are in the code. Which is another way of saying what I said. Unless you work in some crazy place where all of your code is in your database.

blakeyrat

@boomzilla said:

But I can look at code and queries (where it isn't in a sproc) all in the same place, because the queries are in the code.

And that is desirable... why?

boomzilla

@blakeyrat said:

@boomzilla said:
But I can look at code and queries (where it isn't in a sproc) all in the same place, because the queries are in the code.

And that is desirable... why?

You're asking why it's desirable to be able to conveniently read a query when trying to understand a bug? What are you, some kind of CLI anti-usability nut?

blakeyrat

@boomzilla said:

@blakeyrat said:
@boomzilla said:
But I can look at code and queries (where it isn't in a sproc) all in the same place, because the queries are in the code.

And that is desirable... why?

You're asking why it's desirable to be able to conveniently read a query when trying to understand a bug? What are you, some kind of CLI anti-usability nut?

No, I just treat my sprocs like function calls. You pass in the data, and trust that it does the right thing with it.

StoreUser(name, passwordHash, salt) and it gets stored. Why would it be helpful for debugging purposes to see the exact SQL that this performs? If you want to test the sproc, you can do that in your favorite SQL management tool.

Cassidy

@boomzilla said:

Plus, when you're looking at the code, the query is right there, which makes debugging a lot easier.

I feel it's the opposite: you're talking about two different syntaxes, the app code and the SQL code.

When reviewing PHP code, I develop target blindness and completely miss errors in the embedded SQL.

A comparison for Morbs: imagine reading PHP/HTML with embedded javascript and CSS. Now imagine moving them out into javascript events and CSS classes - consider the maintainability aspect of tweaking some CSS without touching the PHP/HTML, etc.

Yes, it's a fairly weak analogy. I've had too many beers.

boomzilla

@blakeyrat said:

No, I just treat my sprocs like function calls. You pass in the data, and trust that it does the right thing with it.

StoreUser(name, passwordHash, salt) and it gets stored. Why would it be helpful for debugging purposes to see the exact SQL that this performs? If you want to test the sproc, you can do that in your favorite SQL management tool.

That's fine. I have no problem with that setup. And for the stuff I use that are sprocs (or views, for that matter), I do the same thing. But it doesn't magically make it more convenient to see everything. You might as well argue that it's just as convenient to keep multiple IDEs open, and look at different parts of your code base in those different IDEs. There may be good reasons to do so (as there is for implementing sprocs), but making it convenient and easy to see it all in one place isn't one of them.

@Cassidy said:

@boomzilla said:
Plus, when you're looking at the code, the query is right there, which makes debugging a lot easier.

I feel it's the opposite: you're talking about two different syntaxes, the app code and the SQL code.

Yes, that's exactly what I'm talking about. Because the bug might be in some SQL or in the code around it.

@Cassidy said:

When reviewing PHP code, I develop target blindness and completely miss errors in the embedded SQL.

I can see how that could be an issue. It's just not one that I have. But then, I'm not doing PHP, so I don't have that going against me.

blakeyrat

@boomzilla said:

That's fine. I have no problem with that setup.

Then why grill me over it?

OH WAIT Boomzilla, duh.

boomzilla

@blakeyrat said:

@boomzilla said:
That's fine. I have no problem with that setup.

Then why grill me over it?

OH WAIT Boomzilla, duh.

It's fascinating watching you post. And not comprehend the posts around you. Or that you reply to. Or that you wrote previously.

To recap: I mentioned why I preferred working differently than you. Then you challenged me with wild accusations. Then you ignored what I'd previously written, when you said, "Why is that desirable?" after I just said why. I can totally see why you like to watch cartoons and play video games. Real life is just too incomprehensible.

Peraninth

@boomzilla said:

@blakeyrat said:
@boomzilla said:
That's fine. I have no problem with that setup.

Then why grill me over it?

OH WAIT Boomzilla, duh.

It's fascinating watching you post. And not comprehend the posts around you. Or that you reply to. Or that you wrote previously.

To recap: I mentioned why I preferred working differently than you. Then you challenged me with wild accusations. Then you ignored what I'd previously written, when you said, "Why is that desirable?" after I just said why. I can totally see why you like to watch cartoons and play video games. Real life is just too incomprehensible.

Ahahah. This is a classic blakey post. Reading the thread I woulda sworn he was grilling you. Good thing he clarified for us! Also, some of us play video games because we are socially inept and can't talk to real people (especially girls). There's no need to stereotype, sheesh.

Speakerphone_Dude

@blakeyrat said:

@boomzilla said:
But I can look at code and queries (where it isn't in a sproc) all in the same place, because the queries are in the code.

And that is desirable... why?

Because if stored procedures were to be used, it would be easy for the organization to maintain data maps and perform data lineage WITHOUT purchasing an expensive codebase analysis tool like Becubic. That's a bad thing because this tool is very cool and does pie charts.

Speakerphone_Dude

@Peraninth said:

some of us play video games because we are socially inept and can't talk to real people (especially girls).

Wait, do you mean that "some of us" includes girls that play video games and are socially inept? If so, could you tell us what they are wearing right now?

oheso

@Zylon said:

People who realize that backticks in place of apostrophes look idiotic care.

QFT. I'm seriously considering physically removing that key for every user who is not a developer.

oheso

@boomzilla said:

CLI anti-usability nut?

Excellent. I'm going to see if that domain is still available.

oheso

@Cassidy said:

Yes, it's a fairly weak analogy. I've had too many beers.

Any analogy involving beers is, ipso facto, a good one.

Speakerphone_Dude

@oheso said:

@Zylon said:
People who realize that backticks in place of apostrophes look idiotic care.

QFT. I'm seriously considering physically removing that key for every user who is not a developer.

If you remove that key, you also remove the tilde, preventing countless people to draw attention to their posts in the escorts section of backpage.com.

Jaime

@morbiuswilters said:

An aside, since I respect your opinion: can you explain the benefit of using stored procedures for every single database access? I've worked with a couple of people who insist on doing this and it drives me crazy. When I pressed them for a reason, they could never provide one other than "It makes things cleaner". Cleaner how? Why are they doing this?

I'm not saying stored procedures aren't sometimes useful or the right choice, but these people would write a stored procedure to wrap every single select they did. This was a nightmare if you needed to modify the schema. Add in the fact that they didn't use version control for DDL files and you end up with an unhappy Morbs having to log into a bunch of servers to update stored procedures when fixing their frequent goddamn bugs.

I can't. I firmly believe that there should be a place to go to find all of the data access methods, but I also believe that place is the DAL, not a list of procs in the database. Even if, for some technical reason (like maybe being forced to use SQL 6.5 at gunpoint), I decided to use stored procedure for everything, the DAL would still be my go-to list for all database accesses.

Stored procedures are fine when they are a good idea, but always using them for no other reason than convention is stupid. The reasons presented here so far are all weak:

@Speakerphone Dude said:

On SQL Server because of the ownership chain you can prevent users from seeing or accessing tables while allowing them to execute a stored procedure that accesses those tables (if the person who created the procedure has the permission to access the tables). This is a convenient way to put an abstraction layer within the database, allowing modifications to the underlying schema without impacting the client applications as long as the procedures signature does not change.

This only matters if the user is directly accessing the database. Everything I've written for the past fifteen years has been a web app or a three-tier app, neither allow the end user to even log into the database, let alone modify data, regardless of the use of stored procedures. @Speakerphone Dude said:

Since the dependencies between stored procedures and underlying objects is maintained in a system catalog this makes maintenance and impact analysis easier.

Delayed resolution starting in SQL 7 made this much less useful, and it's gotten worse in every version. I would never trust the database's dependency tree. @Speakerphone Dude said:

Two more benefits of stored procedures:

You can maintain stored procedures as individual artifacts (such as using Visual Studio Database Projects), which opens the door to database unit tests

Stored procedures allow the server to reuse execution plans, which has a positive impact on performance

Yawn...

A DAL is a class library that is just as easy to unit test. If anything, stored procedures are harder to unit test.
Ad-hoc SQL has had this benefit in MS SQL since 1997. The only caching benefit of stored procedures is the fact that you can force it not to cache the execution plan (with recompile), which is impossible to do without stored procedures.

@blakeyrat said:

You can debug your sproc without even leaving the database environment. Or do you work in some crazy place where you can create sprocs, but can't execute them?

That's more of a "lack of drawback" than a benefit. Where I work, we can execute procs, but we can't debug them (debugging requires being in the sysadmin role and developers in the sysadmin role means headaches for the DBAs, at least that's what the DBAs say). @blakeyrat said:

I do that so it insulates the schema from the application. It's easier to keep the database flexible if you don't have to make app changes at the same time you make schema changes, and sprocs allow that easily.

Although this is 100% true, in order for the argument to work, you would need to show that stored procedures are better than the alternatives, not just that it's possible to use stored procedure for data access encapsulation. I would argue that stored procedures are an inferior solution to this problem. If you don't agree, tell me how a stored procedure oriented developer would get a list of all code that calls a specific procedure?

Jaime

@Speakerphone Dude said:

@blakeyrat said:
@boomzilla said:
But I can look at code and queries (where it isn't in a sproc) all in the same place, because the queries are in the code.

And that is desirable... why?

Because if stored procedures were to be used, it would be easy for the organization to maintain data maps and perform data lineage WITHOUT purchasing an expensive codebase analysis tool like Becubic. That's a bad thing because this tool is very cool and does pie charts.

How do you find out what applications call what procedures? You've simply replaced one hard problem with another one.

blakeyrat

@Jaime said:

Although this is 100% true, in order for the argument to work, you would need to show that stored procedures are better than the alternatives, not just that it's possible to use stored procedure for data access encapsulation. I would argue that stored procedures are an inferior solution to this problem.

You win, primarily because I no longer give a shit. You win a no-prize.

@Jaime said:

If you don't agree, tell me how a stored procedure oriented developer would get a list of all code that calls a specific procedure?

Control-F.

Speakerphone_Dude

@Jaime said:

Everything I've written for the past fifteen years has been a web app or a three-tier app, neither allow the end user to even log into the database, let alone modify data, regardless of the use of stored procedures.

Over those 15 years of no doubt quality work you've done, where you always in a situation where you could control what other applications were doing with the database? If yes, then your opinion is irrelevant; if no, then your opinion is wrong.

Speakerphone_Dude

@Jaime said:

@Speakerphone Dude said:
@blakeyrat said:
@boomzilla said:
But I can look at code and queries (where it isn't in a sproc) all in the same place, because the queries are in the code.

And that is desirable... why?

Because if stored procedures were to be used, it would be easy for the organization to maintain data maps and perform data lineage WITHOUT purchasing an expensive codebase analysis tool like Becubic. That's a bad thing because this tool is very cool and does pie charts.
How do you find out what applications call what procedures? You've simply replaced one hard problem with another one.

Ask your local DBA about traces, input buffer and other marvels. And ask your dev team lead how to use connection strings to provide the application name to the database engine.

The_Assimilator

@morbiuswilters said:

@The_Assimilator said:
@CarnivorousHippie said:
@KattMan said:
Bad move really, just use parameterized queries and don't worry about it.
Just getting them to use stored procedures instead of building SQL in ASP pages is a battle I''ve long since given up on.

I'm very disappointed you haven't purposely injected SQL to drop one of the main tables or even the DB. You only need to do it once, because after the system has been down for 3 days and they've been crapped on constantly during that time, they'll never write inline SQL again.

That would probably get you fired most places. And the lesson learned would be "Don't inject SQL" not "Don't permit SQL injection"..

C'mon, you've never "accidentally" pasted a drop database command into a comments field? *whistles innocently*

@Jaime said:

@CarnivorousHippie said:
@Jaime said:
How many times do this have to be said... Parameterizing protects against SQL Injection, stored procedures do not. Properly parameterized inline SQL in injection-proof, stored procedures called without parameterization are vulnerable to SQL Injection.

It didn't need to be said at all. Using parameters to thwart SQL injection and using stored procedures to separate data/interface are different concerns.

Apparently, it does ...

@The_Assimilator said:
I'm very disappointed you haven't purposely injected SQL to drop one of the main tables or even the DB. You only need to do it once, because after the system has been down for 3 days and they've been crapped on constantly during that time, they'll never write inline SQL again.

... The_Assimilator is merging those concerns together. It's not an uncommon problem, about 75% of the people I talk to get it wrong.

99.99999~% of the time, people writing inline SQL don't use parameters, hence providing attack vectors for SQL injection. Sprocs require you to use parameters to pas data to them, implicitly closing 99.99999~% of these potential security holes. (Unless you're using sp_execsql or similar inside your sproc, in which case you have to be far more careful.)

As for the sprocs vs inline SQL style debate... it's not really a debate anymore with frameworks like (N)Hibernate and Entity Framework that generate safe inline SQL for you. I'd go as far to say that the only time you need sprocs nowadays is when you're doing heavy lifting in your DB. (Unless of course you're stuck with something like Delphi, you poor sod.)

Jaime

@The_Assimilator said:

99.99999~% of the time, people writing inline SQL don't use parameters, hence providing attack vectors for SQL injection. Sprocs require you to use parameters to pas data to them, implicitly closing 99.99999~% of these potential security holes. (Unless you're using sp_execsql or similar inside your sproc, in which case you have to be far more careful.)

This is where you are wrong. It's very easy to parameterize inline SQL with ADO.Net, and a lot of people do it right. Telling someone to use stored procedures when you really mean to tell them to use parameters, in the hope that they get parameterization right, is stupid, wastes a learning opportunity, and creates a cargo cult atmosphere. Nothing is more dangerous than cargo-cultism.

Jaime

@Speakerphone Dude said:

@Jaime said:
How do you find out what applications call what procedures? You've simply replaced one hard problem with another one.
Ask your local DBA about traces, input buffer and other marvels. And ask your dev team lead how to use connection strings to provide the application name to the database engine.

Your brilliant idea for traceability is to tell the devs to use profiler? Here is what your plan would look like in action:

Dev wants to modify a procedure and needs to know what calls it.
Dev starts up SQL Profiler and sets a filter to capture only calls to the stored procedure in question.
Dev runs through a manual 100% code coverage test to find all calls to proc.
Dev gets fired for taking six weeks to modify a stored procedure.

That's like a thousand times worse than just doing a text search over the codebase, which was already only a marginally OK strategy.

heterodox

@Jaime said:

Nothing is more dangerous than cargo-cultism.

Except velociraptors.

pkmnfrk

@Jaime said:

@The_Assimilator said:
99.99999~% of the time, people writing inline SQL don't use parameters, hence providing attack vectors for SQL injection. Sprocs require you to use parameters to pas data to them, implicitly closing 99.99999~% of these potential security holes. (Unless you're using sp_execsql or similar inside your sproc, in which case you have to be far more careful.)
This is where you are wrong. It's very easy to parameterize inline SQL with ADO.Net, and a lot of people do it right. Telling someone to use stored procedures when you really mean to tell them to use parameters, in the hope that they get parameterization right, is stupid, wastes a learning opportunity, and creates a cargo cult atmosphere. Nothing is more dangerous than cargo-cultism.

string sql = "EXEC sp_do_something(" + Request["ID"] + ", '" + Request["Name"] + "');";

See? Much safer.

Cassidy

@Jaime said:

How do you find out what applications call what procedures?

Carefully-maintained and version-controlled documentation that accurately describes --

-- shit, I can't keep a straight face when typing that. Sorry.... I tried.

Jaime

@Cassidy said:

@Jaime said:
How do you find out what applications call what procedures?

Carefully-maintained and version-controlled documentation that accurately describes --

-- shit, I can't keep a straight face when typing that. Sorry.... I tried.

Funny you should mention that... Do to the fact that SQL Server cannot keep an accurate list of dependencies because of deferred name resolution, we have a standard that all database objects have a comment header that includes dependency information. An automated documentation tool gathers this information to keep our documentation up to date and we have tools to walk dependency trees to answer "what would be affected" questions. We use code reviews to manage adherence to the standard, and we are very successful at doing so.

I've seriously considered pushing this standard up the stack to handle the question of what code calls what procedures. The only reason I haven't, is our data access layer design makes it very easy to use the Visual Studio "Find All References..." feature for this.

Cassidy

@Jaime said:

Do to the fact

"due", Shirley?

@Jaime said:

... we have a standard that all database objects have a comment header that includes dependency information. An automated documentation tool gathers this information to keep our documentation up to date and we have tools to walk dependency trees to answer "what would be affected" questions. We use code reviews to manage adherence to the standard, and we are very successful at doing so.

That's... staggeringly utopian, yet perfectly achievable and I should not express surprise and disbelief that someone's managed to pull it off.

Kudos, young Sir. I now view your organisation in slightly different light than previously ~~prejudged~~ imagined.

Mcoder

@morbiuswilters said:

It's not in the database drivers and it's been off by default for years (and any sensible person would have turned it off even when it was on by default). Magic quotes just adds slashes to user-supplied parameters, such as GET and POST vars. It's possible that there was some other bug in your database driver which was inserting slashes, I dunno. I've used prepared statements in PHP with Postgres and MySQL for many years without issue.

Well, NOW I know the problem was not in the database drivers. But I send some data through POST, read it and store on the database; query the database and it's wrong... How could I imagine something was mangling the data received from POST? The only thing with a 'thick' abstraction layer here is the database, any minimaly competent person would just map the variables to point to the POST data and not even touch it.

And yes, the fact that it defaults to off bugs me, it wasn't that long ago. But I didn't setup the environment, so I have no idea why it was on. (I had no idea somebody could ever think about such an aberration.)