Fun with single quotes
-
The internally-developed case management system here has had an issue for the longest time with single quotes; namely, if you enter a note like:
I've rendered the foo and John'll approve.
What gets displayed in the system is:
I''ve rendered the foo and John''ll approve.
Obvious issue with inserting data in SQL, but no one has bothered to try to fix it for years. Until now. Now, of course, you get:
I''''ve rendered the foo and John''''ll approve.
-
I remember these, it's simply trying to escape the single quote by doubling it.
Bad move really, just use parameterized queries and don't worry about it.
-
That'''s brillant.
-
I think they got confused when they heard about "Worse is better."
-
@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.
-
@boomzilla said:
I think they got confused when they heard about "Worse is better."
Or about "object-oriented". Or about "data abstraction". Or about... well, the list goes on.
-
Put in a backslash, and watch the number double on every iteration!
-
@KattMan said:
I remember these, it's simply trying to escape the single quote by doubling it.
Which is how you escape a single quote in most RDBMS software. The problem is that it appears to be doing it twice...
-
@CarnivorousHippie said:
Closed-captioning on The Big Bang Theory used to suffer from this problem. I think they finally got around to fixing it this last season.The internally-developed case management system here has had an issue for the longest time with single quotes; namely, if you enter a note like:
I've rendered the foo and John'll approve.
What gets displayed in the system is:
I''ve rendered the foo and John''ll approve.
-
@da Doctah said:
@CarnivorousHippie said:
Closed-captioning on The Big Bang Theory used to suffer from this problem. I think they finally got around to fixing it this last season.The internally-developed case management system here has had an issue for the longest time with single quotes; namely, if you enter a note like:
I've rendered the foo and John'll approve.
What gets displayed in the system is:
I''ve rendered the foo and John''ll approve.
The ironing is delicious.
-
There's a website I used to visit where the single quotes proliferated like mad - it seemed like every time the content was edited, the number of quotes doubled, so eventually you wound up with sixteen single quotes in a row!
-
@ekolis said:
There's a website I used to visit where the single quotes proliferated like mad - it seemed like every time the content was edited, the number of quotes doubled, so eventually you wound up with sixteen single quotes in a row!
I worked on a shitty, proprietary CMS that did this with backslashes because PHP's magic quotes was on. The "solution" was to strip all backslashes from data being saved to the database (we had our own custom escaping scheme that replaced single quotes with a string of gibberish characters when going into the DB and converted the gibberish string back into single quotes on the way out.. it was as retarded as it sounds.)
-
This is 90% of what's wrong with PHP. It tried for a bit to help people avoid SQL Injection Errors by randomly quoting things at the entirely wrong level (form input to value), rather than making it easy to use parameterized SQL (which it does now).
But what that taught an entire generation of PHP programmers is "I don't have to worry about SQL injection - the language somehow magically makes it safe", and they also broaden that to HTML escaping. And thus, 90% of the crap from website security is PHP programmers who don't think and weren't trained to do it right.
-
@realmerlyn said:
This is 90% of what's wrong with PHP. It tried for a bit to help people avoid SQL Injection Errors by randomly quoting things at the entirely wrong level (form input to value), rather than making it easy to use parameterized SQL (which it does now).
But what that taught an entire generation of PHP programmers is "I don't have to worry about SQL injection - the language somehow magically makes it safe", and they also broaden that to HTML escaping. And thus, 90% of the crap from website security is PHP programmers who don't think and weren't trained to do it right.
It was always easy to escape SQL in PHP. I agree that magic quotes was idiotic, but it's hardly as big of a deal as you claim. Stupid people who don't care about security are rampant in every language I've used. Do you know how much truly hideous Java I've seen? Or C where the "solution" to buffer overflows was just to malloc a few hundred k because "nobody will try to send that much data".
-
@CarnivorousHippie said:
You still have to parameterize stored procedure calls or you'll have exactly the same problem. Stored procedures do nothing to mitigate SQL injection.@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.
-
Oh shit,it''''''''''''''''''''''''''''''''''s getting worse!
-
I see that fairly often, that a program escapes quotes twice entering them into the DB, which leads to doubled quotes in the output. What''''''''''''''''''''''''''''''''''''''''''''s fun is when it''''''''''''''''''''''''''''''''''''''''''''s a system that allows editing or quoting/copying, and with every change/copy, the text gets double-escaped again, and the quotes double again...
-
The problem seems to be - quite simply - that the translation process is incomplete: for every escape routine, there needs to be the equivalent unescape routine. Most languages have libraries to cope with this (add_slashes/strip_slashes and the like).
I've seen this happen in some home-rolled blogs where the content hasn\'t been properly unescaped.
-
What's wrong with
"It's great Tom's quote replacement".Replace("'", "''").Replace("''''", "''")???
-
What if the user wanted a legitimate string of four apostrophes in a row? The first replace doubles it to 8, the second halves it back down to four; no escaping has occurred.
-
Brillant. A clbuttic mistake, not checking edge cases.
-
@morbiuswilters said:
@ekolis said:
There's a website I used to visit where the single quotes proliferated like mad - it seemed like every time the content was edited, the number of quotes doubled, so eventually you wound up with sixteen single quotes in a row!
I worked on a shitty, proprietary CMS that did this with backslashes because PHP's magic quotes was on. The "solution" was to strip all backslashes from data being saved to the database (we had our own custom escaping scheme that replaced single quotes with a string of gibberish characters when going into the DB and converted the gibberish string back into single quotes on the way out.. it was as retarded as it sounds.)
Hey, the only time I actualy tried to do something important in PHP I spent a lot of time trying to discover whatever is that Fing bug in the database drivers that kept inserting slashes on every quote character I sent to my prepared statements. I also decided to strip the slashes, but from the presentation layer.
Nice to know that it is a feature. Thus PHP was really not intended to work with prepared statements.
-
@Mcoder said:
@morbiuswilters said:
@ekolis said:
There's a website I used to visit where the single quotes proliferated like mad - it seemed like every time the content was edited, the number of quotes doubled, so eventually you wound up with sixteen single quotes in a row!
I worked on a shitty, proprietary CMS that did this with backslashes because PHP's magic quotes was on. The "solution" was to strip all backslashes from data being saved to the database (we had our own custom escaping scheme that replaced single quotes with a string of gibberish characters when going into the DB and converted the gibberish string back into single quotes on the way out.. it was as retarded as it sounds.)
Hey, the only time I actualy tried to do something important in PHP I spent a lot of time trying to discover whatever is that Fing bug in the database drivers that kept inserting slashes on every quote character I sent to my prepared statements. I also decided to strip the slashes, but from the presentation layer.
Nice to know that it is a feature. Thus PHP was really not intended to work with prepared statements.
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.
-
@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).
In fact, with the current version it's finally [i]gone[/i]. PHP's developers freely admit it was a mistake.
-
@Watson said:
@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).
In fact, with the current version it's finally gone. PHP's developers freely admit it was a mistake.Yeah, it was a moronic "feature", that's for sure.
-
@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.
-
@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.
Now, if only there was some cartoon depicting such a situation in a humourous yet satirical manner which we could all identify with. Why, it would cement those concepts surrounding the dangers of SQL injections perfectly!
-
@Cassidy said:
@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.
Now, if only there was some cartoon depicting such a situation in a humourous yet satirical manner which we could all identify with. Why, it would cement those concepts surrounding the dangers of SQL injections perfectly!
I don't give a fuck about security in this case, since the app is internal use only. What I do give a fuck about is that if you don't hold developers to high standards on every project they work on, they will allow their poor standards to infect every part of your codebase they touch.
-
@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"..
-
@morbiuswilters said:
Stupid people who don't care about security are rampant in every language I've used. Do you know how much truly hideous Java I've seen? Or C where the "solution" to buffer overflows was just to malloc a few hundred k because "nobody will try to send that much data".
We had a "developer", outside of the development section (a legacy from a long bygone era), who had a few "peculiarities",For one thing, he was the worst programmer I've ever seen. Probably, any of our three cats would be better at it. Where to start? Oh dear... the least problem was the fact that he used GCC 2.95.1, which has a particular bug so that a variable declared inside a for-loop would remain in scope even after the loop, and which meant that many programs would stop compiling just by upgrading the compiler. And obviously, there were no makefiles.
One could also mention the binary sort he obviously downloaded from the internet, but managed to f*** up by clearly not understanding what was going on, and where he sorted telephone numbers by converting them to floating point numbers - never mind that there's a string compare in C. That wasn't the biggest problem either.
Or there was the '8 report', which did a 'select 8 from some_table' and which was sent for several years to many people inside the company, including the CEO. Not even that was the biggest problem.
No, the biggest problem was that he allocated absolutely everything statically (on the heap), and never did any boundary checking. So there was a program that ran over a set of data and produced some or other result, or inserted it into a database. Naturally, all data would be loaded into memory before any processing took place, and as the data sets grew, there would be more and more crashes due to segmentation violations.
His solution? He 'rewrote' the application by allocating 100 times as much memory, and if the first one crashed, the second one ran. They actually had to buy bigger and rather expensive Sun hardware to run the crap code this guy came up with.
-
@The_Assimilator said:
I don't give a fuck about security in this case, since the app is internal use only. What I do give a fuck about is that if you don't hold developers to high standards on every project they work on, they will allow their poor standards to infect every part of your codebase they touch.
In my experience, developers are very rarely held to high standards, because the person who would have the authority to uphold these standards usually doesn't know enough of development to make that qualification.
-
@Severity One said:
Or there was the '8 report', which did a 'select 8 from some_table' and which was sent for several years to many people inside the company, including the CEO.
This would have been useful to Bernie Madoff. Absolutely consistent performance reporting, every year.
-
@morbiuswilters said:
@ekolis said:
There's a website I used to visit where the single quotes proliferated like mad - it seemed like every time the content was edited, the number of quotes doubled, so eventually you wound up with sixteen single quotes in a row!
I worked on a shitty, proprietary CMS that did this with backslashes because PHP's magic quotes was on. The "solution" was to strip all backslashes from data being saved to the database (we had our own custom escaping scheme that replaced single quotes with a string of gibberish characters when going into the DB and converted the gibberish string back into single quotes on the way out.. it was as retarded as it sounds.)
This really begs the question of why you would go to so many lengths to implement special routines to work around the magic quotes setting instead of just turning it off? That would seem to be more of a WTF.
-
@Cassidy said:
The problem seems to be - quite simply - that the translation process is incomplete: for every escape routine, there needs to be the equivalent unescape routine.
Unless you use the real magic quotes, aka "backquotes". They are magic because they look like they fixed the problem without requiring unescaping; after all, if your output says
Terence Trent D`Arbyinstead ofTerence Trent D'Arbynobody cares... unless you pass the value to a shell script.
-
@The_Assimilator said:
I don't give a fuck about security in this case, since the app is internal use only. What I do give a fuck about is that if you don't hold developers to high standards on every project they work on, they will allow their poor standards to infect every part of your codebase they touch.
Yeah, because there are never any malicious insiders.
-
@The_Assimilator said:
@CarnivorousHippie 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.
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.@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.
-
@Severity One said:
No, the biggest problem was that he allocated absolutely everything statically (on the heap), and never did any boundary checking. So there was a program that ran over a set of data and produced some or other result, or inserted it into a database. Naturally, all data would be loaded into memory before any processing took place, and as the data sets grew, there would be more and more crashes due to segmentation violations.
His solution? He 'rewrote' the application by allocating 100 times as much memory, and if the first one crashed, the second one ran. They actually had to buy bigger and rather expensive Sun hardware to run the crap code this guy came up with.
Ouch. Sounds like a particularly nasty case of C Programmer's Disease.
I had a former cow-orker who'd do stuff like that all the time. He did a lot of static allocation, loved putting related data in separate arrays instead of a single array of either a struct or a class, wouldn't write structs and classes so we could stick 'em in a vector or map, and didn't think it at all strange to have to modify five different files and rebuild to add a single option to a dropdown, even though no basic functionality had changed.
I no longer work with him. Thank God.
-
@Jaime said:
You still have to parameterize stored procedure calls or you'll have exactly the same problem. Stored procedures do nothing to mitigate SQL injection.
My first thought was, "Duh! Who's gonna go through the trouble of implementing procs and not do that?"
My next thought was, "Duh, my boss."
-
@Cassidy said:
Now, if only there was some cartoon depicting such a situation in a humourous yet satirical manner which we could all identify with. Why, it would cement those concepts surrounding the dangers of SQL injections perfectly!
<3
-
@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.
-
@CarnivorousHippie said:
@Jaime said:
Apparently, it does ...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.
@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.
-
@Jaime said:
@CarnivorousHippie said:
@Jaime said:
Apparently, it does ...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.
@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.
What alternative to "inline SQL" are you thinking of? Calling "exec sp_foo 'bar', 'baz'" still counts as inline as I choose to understand the term.
-
@Speakerphone Dude said:
Unless you use the real magic quotes, aka "backquotes". They are magic because they look like they fixed the problem without requiring unescaping; after all, if your output says
Terence Trent D`Arbyinstead ofTerence Trent D'Arbynobody cares...People who realize that backticks in place of apostrophes look idiotic care.
-
@ASheridan said:
@morbiuswilters said:
@ekolis said:
There's a website I used to visit where the single quotes proliferated like mad - it seemed like every time the content was edited, the number of quotes doubled, so eventually you wound up with sixteen single quotes in a row!
I worked on a shitty, proprietary CMS that did this with backslashes because PHP's magic quotes was on. The "solution" was to strip all backslashes from data being saved to the database (we had our own custom escaping scheme that replaced single quotes with a string of gibberish characters when going into the DB and converted the gibberish string back into single quotes on the way out.. it was as retarded as it sounds.)
This really begs the question of why you would go to so many lengths to implement special routines to work around the magic quotes setting instead of just turning it off? That would seem to be more of a WTF.
I guess it was the "shitty" part which prevented them from seeing the forest for the trees.
-
@CarnivorousHippie said:
What alternative to "inline SQL" are you thinking of? Calling "exec sp_foo 'bar', 'baz'" still counts as inline as I choose to understand the term.
Anybody who thinks "inline SQL" and "stored procedure" are mutually exclusive understands the term differently than you do. Since The_Assimilator offered stored procedures as an alternative to inline SQL, he is in that group.Also, your definition of inline SQL isn't very useful, as calling "exec sp_foo @bar, @baz" is still inline SQL by your definition, but injection safe. Heck, your definition allows "sp_executesql 'exec sp_foo @bar, @baz', '@bar varchar(20) @baz varchar(20)', @bar='bar', @baz='baz'" to be called inline SQL, making the term meaningless.
-
@Jaime said:
@CarnivorousHippie said:
What alternative to "inline SQL" are you thinking of? Calling "exec sp_foo 'bar', 'baz'" still counts as inline as I choose to understand the term.
Anybody who thinks "inline SQL" and "stored procedure" are mutually exclusive understands the term differently than you do. Since The_Assimilator offered stored procedures as an alternative to inline SQL, he is in that group.Also, your definition of inline SQL isn't very useful, as calling "exec sp_foo @bar, @baz" is still inline SQL by your definition, but injection safe. Heck, your definition allows "sp_executesql 'exec sp_foo @bar, @baz', '@bar varchar(20) @baz varchar(20)', @bar='bar', @baz='baz'" to be called inline SQL, making the term meaningless.
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.
-
@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 mean a lot of WTFs around here revolve around database schemas that were either designed to be 100% flexible (and thus lose all benefits of SQL in the first place), or schemas with a ton of hacked-in features to support crappy apps.
Oh and for what it's worth, treating "inline SQL" and "stored procedures" as different entities can be due to using ADO.net, which actually does do a sproc call differently than running inline SQL.
-
@morbiuswilters said:
can you explain the benefit of using stored procedures for every single database access?
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. Since the dependencies between stored procedures and underlying objects is maintained in a system catalog this makes maintenance and impact analysis easier.
-
@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.
-
@Jaime said:
@CarnivorousHippie said:
What alternative to "inline SQL" are you thinking of? Calling "exec sp_foo 'bar', 'baz'" still counts as inline as I choose to understand the term.
Anybody who thinks "inline SQL" and "stored procedure" are mutually exclusive understands the term differently than you do. Since The_Assimilator offered stored procedures as an alternative to inline SQL, he is in that group.Also, your definition of inline SQL isn't very useful, as calling "exec sp_foo @bar, @baz" is still inline SQL by your definition, but injection safe. Heck, your definition allows "sp_executesql 'exec sp_foo @bar, @baz', '@bar varchar(20) @baz varchar(20)', @bar='bar', @baz='baz'" to be called inline SQL, making the term meaningless.
I didn't see Assimilator offer stored procedures as an alternative, or even mention them.
My definition of "inline SQL" (for the purposes of this discussion) is orthogonal to being injection-safe. (Again, different concern.) Generally speaking, if I'm building a SQL string in application code instead of using an Command object or hitting a datalayer, then...
You know, you're right. The adjective 'inline' is redundant. If you're using SQL statements in code, of course it's inline.
