12 ms·

My name still causes SQL errors

127 points by slyall 10y ago

stepvhen 10y ago
Very related: http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/ http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-b...
- huuu 10y ago
  I understand what you are saying but this has nothing to do with names but with SQL injections, escaping and prepared statements (the lack of).
  - jameshart 10y ago
    It has everything to do with names. Among the things that programmers sometimes believe about names, as in this case, is that when you stick apostrophes around them they are always a valid SQL string literal.
    - biot 10y ago
      Inexperienced programmers naively believe that about all string input, so the fact they believe it about names isn't particularly interesting.
- Udo 10y ago
  No, it's not related. This is about escaping strings in queries, it should not have anything to do with whether programmers thought about all the things that a string can contain or not. User data inside queries absolutely must be escaped at all times, if not that's not a falsehood in belief, it's a bug. Conversely, when a string is properly escaped, you expect the DB not to die on valid input. Which MySQL/MariaDB totally does, by the way, for example when your users submit emojis into the database. Now that would be a falsehood programmers believe about strings, or names if you will.
draw_down 10y ago
Requisite XCKD reference included. Nerds never fail to be predictable.
pilif 10y ago
Aside of the obvious SQL injection (heck - the way this is set up, it obviously even takes multiple queries in one string - this is pure heaven from an injection standpoint), I'm also wondering what kind of schema this is? Creating an user as a sequence of operations? Why? Imagine the ways this data can get corrupted? What if for some reason only one of the queries executes? What if somebody removes one of the rows for a user but not the other when doing support or maintenance?
- Macuyiko 10y ago
  In a past life, I converted an intranet application for some government department from Oracle/Coldfusion. The whole application, every SQL query taking input, was vulnerable to SQL injection. For many insert operations, I'd find a pattern appearing of first inserting a row with some random value in one of the columns (`temp_key`), then performing a select based on this key to get the row back to know the primary key, and then continue to update other fields in the same row, and adding other records to other tables referencing this primary key. Obviously, transactions was a foreign concept to the original developers. I still remember taking a deep breath once I found a code snippet that would try an insert again if the insert failed because the randomly generated `temp_key` was already used before, a problem that seemed to really start bothering them as the database grew larger...
  - delgaudm 10y ago
    I'm stretching my memory as a "full stack" dev back in the 90s but I seem to recall that CF couldn't do transactions for its first few versions. People were begging for <cftransaction> to become a thing. I also don't remember being able to call an oracle stored procedure from CF in version 1 or 2.. But I'm hazy on that for some reason I remember the ODBC driver not supporting it. As a result I recall resorting to hacky stuff like what you describe. It wouldn't surprise me in the least if there is government cfml code out there from the 90's. It was a challenging time with far fewer tools.
  - INTPenis 10y ago
    That is how I learned to do SQL when I was learning how to code in ASP 2.0 and PHP 17 years ago. It seemd quite natural then and for several years onwards.
atemerev 10y ago
I wonder how everyone in the original thread comments is still falling for this infamous "sanitize input!" trap. If "the input" is sanitized, poor guy would be left without an apostrophe in his name, and rightfully pissed off. Prepared statements are the only way to do it.
- MattBearman 10y ago
  I agree that prepared statements are much better than sanitising inputs, but surely a sanitised version of his name would be Gijs in \'t Veld Leaving the apostrophe in tact
  - oneeyedpigeon 10y ago
    "Sanitising" is a vague term and has different meanings depending on context. All it really means, at minimum, is "transforming data, if necessary, to ensure it won't do 'bad stuff'". That might be escaping it, but it could also be stripping certain characters out of it, or just rejecting it altogether.
  - jameshart 10y ago
    you can't sanitize a name, because a name is not dirty. Sanitization basically means taking a string which purports to conform to a particular grammar, verifying that it does and, if it does not forcing it to. But names don't really have a formal grammar, so you can't 'sanitize' one much (maybe trimming leading and trailing white space and normalizing internal white space runs to a single space? Maybe?). Gijs in \'t Veld is a 'sanitized' string in a grammar which doesn't allow bare apostrophes - such as some string literal syntaxes - and escapes them with a backslash. If someone presents you with a string Gijs in 't Veld with the claim that it conforms to that grammar, they are wrong, and maybe the right thing to do then is to sanitize it as you did. Or you could strip the apostrophe out. Or replace it with a question mark. Your sanitization approach is arbitrary because by definition the string you have been given does not conform to the grammar it purports to so you do not know what to do with that bare apostrophe. Sanitization is not 'representation preserving' because it converts broken, ungrammatical strings (which therefore have undefined meaning) into grammatical strings (which have a defined meaning that you hope is what was intended). But this string hasn't been presented as a string conforming to that grammar. It's been presented as a name, which is essentially an arbitrary valid Unicode string. The process of converting it to the restricted grammar with no bare apostrophes but retaining representation of the same underlying string is not 'sanitization' but 'encoding'. It is representation preserving (so long as the grammar to which we are encoding is capable of representing the string). Failure to grasp the differences between these operations is what causes people to wind up sending people like this poor gentleman emails addressed to Gijs in \&#37;t Veld
chris_wot 10y ago
This guy isn't just a tester, he's the test data.
ergl 10y ago
I know this too well. Some pages will tell me that my name or address is 'invalid' because it contains spaces or forbidden characters. I'm sorry, but 'ñ', apostrophes and accent marks are not forbidden, and no name should be ever 'invalid'. How can a name even be 'invalid'? Sometimes I think too many sites assume (wrongly) that all customers will be an average english-speaking, US-resident person.
- lmm 10y ago
  Sites don't (or at least shouldn't) have any obligation to serve everyone. Most people are content to render their name in plain ASCII. How much extra are you willing to pay to be able to use those symbols? Or do you expect everyone else to subsidize you?
  - atomwaffel 10y ago
    Certainly, most websites have no obligation to serve me as a customer, but if they tell me that my name is "invalid", they shouldn't be surprised if I take my custom to a competitor who isn't quite so rude. But you seem to be missing the fact that "Gijs in 't Veld" is perfectly valid Ascii, as would be names like "O'Leary" or "O'Reilly" that are perhaps more common in the English-speaking world.
    - candu 10y ago
      And some websites, such as government portals to various departments and services, do have implicit (and often even explicit) obligations to serve everyone. I wonder how well GP would take it if, say, the IRS webpay or DMV sites (or their equivalent wherever GP happens to live) balked at their name.
      - atomwaffel 10y ago
        Yes, government services was what I thought of when I wrote "most websites". Then again, I feel like those tend to be the worst user experience offenders (which is what this comes down to). It took me several attempts to mangle my home address into something the US visa application form would accept and I've seen ö turned into Ã¶ on my own country's official websites so many times that I can remember the mojibake. I wonder why that is. I guess part of the reason is that government doesn't need to invest heavily in UX because there is no threat of people going elsewhere – when you need a passport, you need a passport, and you're not going to order one from Amazon. In fact, there's probably a disincentive for government to continuously improve their UX precisely because any changes to a system that's in place and working well enough only cost them money. Are there any counterexamples of government websites that offer good user experience?
raubitsj 10y ago
Good ole Little Bobby Tables https://xkcd.com/327/ https://xkcd.com/327/
- wagglycocks 10y ago
  I feel like that joke is so widely known that it can be retired, or at least, referenced without needing the link.
83457 10y ago
That reminds me of the approach taken by many contact forms for members of the US congress. I believe the vendors have to follow certain security processes which includes sending the user to a 404 page if certain malicious attempts are detected. The end result in many cases is that there is a list of words that can not be submitted successfully through their form that changes over time and just check in certain fields based apparently on however the vendor wants to implement the security measure. Is your name Walter? Sorry but you sound like a sql injection attack so we will redirect you and the message you just spent an hour on over to a 404 page.
icedchai 10y ago
Prepared statements are hard. They've only been around for decades.
greenleafjacob 10y ago
Prepared statements are actually worse than escaping in Postgres because prepared statements can sometimes use the wrong query plan. Refer to the documentation: > In some situations, the query plan produced for a prepared statement will be inferior to the query plan that would have been chosen if the statement had been submitted and executed normally. This is because when the statement is planned and the planner attempts to determine the optimal query plan, the actual values of any parameters specified in the statement are unavailable. PostgreSQL collects statistics on the distribution of data in the table, and can use constant values in a statement to make guesses about the likely result of executing the statement. Since this data is unavailable when planning prepared statements with parameters, the chosen plan might be suboptimal. Sometimes this can be more than "suboptimal" but actually an entirely different index and orders of magnitude worse performance.

127 points by slyall 10y ago

Very related: http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/ http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-b...

huuu 10y ago
I understand what you are saying but this has nothing to do with names but with SQL injections, escaping and prepared statements (the lack of).
- jameshart 10y ago
  It has everything to do with names. Among the things that programmers sometimes believe about names, as in this case, is that when you stick apostrophes around them they are always a valid SQL string literal.
  - biot 10y ago
    Inexperienced programmers naively believe that about all string input, so the fact they believe it about names isn't particularly interesting.
Udo 10y ago
No, it's not related. This is about escaping strings in queries, it should not have anything to do with whether programmers thought about all the things that a string can contain or not. User data inside queries absolutely must be escaped at all times, if not that's not a falsehood in belief, it's a bug. Conversely, when a string is properly escaped, you expect the DB not to die on valid input. Which MySQL/MariaDB totally does, by the way, for example when your users submit emojis into the database. Now that would be a falsehood programmers believe about strings, or names if you will.

draw_down 10y ago

Requisite XCKD reference included. Nerds never fail to be predictable.

pilif 10y ago

Aside of the obvious SQL injection (heck - the way this is set up, it obviously even takes multiple queries in one string - this is pure heaven from an injection standpoint), I'm also wondering what kind of schema this is? Creating an user as a sequence of operations? Why? Imagine the ways this data can get corrupted? What if for some reason only one of the queries executes? What if somebody removes one of the rows for a user but not the other when doing support or maintenance?

Macuyiko 10y ago
In a past life, I converted an intranet application for some government department from Oracle/Coldfusion. The whole application, every SQL query taking input, was vulnerable to SQL injection. For many insert operations, I'd find a pattern appearing of first inserting a row with some random value in one of the columns (`temp_key`), then performing a select based on this key to get the row back to know the primary key, and then continue to update other fields in the same row, and adding other records to other tables referencing this primary key. Obviously, transactions was a foreign concept to the original developers. I still remember taking a deep breath once I found a code snippet that would try an insert again if the insert failed because the randomly generated `temp_key` was already used before, a problem that seemed to really start bothering them as the database grew larger...
- delgaudm 10y ago
  I'm stretching my memory as a "full stack" dev back in the 90s but I seem to recall that CF couldn't do transactions for its first few versions. People were begging for <cftransaction> to become a thing. I also don't remember being able to call an oracle stored procedure from CF in version 1 or 2.. But I'm hazy on that for some reason I remember the ODBC driver not supporting it. As a result I recall resorting to hacky stuff like what you describe. It wouldn't surprise me in the least if there is government cfml code out there from the 90's. It was a challenging time with far fewer tools.
- INTPenis 10y ago
  That is how I learned to do SQL when I was learning how to code in ASP 2.0 and PHP 17 years ago. It seemd quite natural then and for several years onwards.

atemerev 10y ago

I wonder how everyone in the original thread comments is still falling for this infamous "sanitize input!" trap. If "the input" is sanitized, poor guy would be left without an apostrophe in his name, and rightfully pissed off. Prepared statements are the only way to do it.

MattBearman 10y ago
I agree that prepared statements are much better than sanitising inputs, but surely a sanitised version of his name would be Gijs in \'t Veld Leaving the apostrophe in tact
- oneeyedpigeon 10y ago
  "Sanitising" is a vague term and has different meanings depending on context. All it really means, at minimum, is "transforming data, if necessary, to ensure it won't do 'bad stuff'". That might be escaping it, but it could also be stripping certain characters out of it, or just rejecting it altogether.
- jameshart 10y ago
  you can't sanitize a name, because a name is not dirty. Sanitization basically means taking a string which purports to conform to a particular grammar, verifying that it does and, if it does not forcing it to. But names don't really have a formal grammar, so you can't 'sanitize' one much (maybe trimming leading and trailing white space and normalizing internal white space runs to a single space? Maybe?). Gijs in \'t Veld is a 'sanitized' string in a grammar which doesn't allow bare apostrophes - such as some string literal syntaxes - and escapes them with a backslash. If someone presents you with a string Gijs in 't Veld with the claim that it conforms to that grammar, they are wrong, and maybe the right thing to do then is to sanitize it as you did. Or you could strip the apostrophe out. Or replace it with a question mark. Your sanitization approach is arbitrary because by definition the string you have been given does not conform to the grammar it purports to so you do not know what to do with that bare apostrophe. Sanitization is not 'representation preserving' because it converts broken, ungrammatical strings (which therefore have undefined meaning) into grammatical strings (which have a defined meaning that you hope is what was intended). But this string hasn't been presented as a string conforming to that grammar. It's been presented as a name, which is essentially an arbitrary valid Unicode string. The process of converting it to the restricted grammar with no bare apostrophes but retaining representation of the same underlying string is not 'sanitization' but 'encoding'. It is representation preserving (so long as the grammar to which we are encoding is capable of representing the string). Failure to grasp the differences between these operations is what causes people to wind up sending people like this poor gentleman emails addressed to Gijs in \&#37;t Veld

chris_wot 10y ago

This guy isn't just a tester, he's the test data.

ergl 10y ago

I know this too well. Some pages will tell me that my name or address is 'invalid' because it contains spaces or forbidden characters. I'm sorry, but 'ñ', apostrophes and accent marks are not forbidden, and no name should be ever 'invalid'. How can a name even be 'invalid'? Sometimes I think too many sites assume (wrongly) that all customers will be an average english-speaking, US-resident person.

lmm 10y ago
Sites don't (or at least shouldn't) have any obligation to serve everyone. Most people are content to render their name in plain ASCII. How much extra are you willing to pay to be able to use those symbols? Or do you expect everyone else to subsidize you?
- atomwaffel 10y ago
  Certainly, most websites have no obligation to serve me as a customer, but if they tell me that my name is "invalid", they shouldn't be surprised if I take my custom to a competitor who isn't quite so rude. But you seem to be missing the fact that "Gijs in 't Veld" is perfectly valid Ascii, as would be names like "O'Leary" or "O'Reilly" that are perhaps more common in the English-speaking world.
  - candu 10y ago
    And some websites, such as government portals to various departments and services, do have implicit (and often even explicit) obligations to serve everyone. I wonder how well GP would take it if, say, the IRS webpay or DMV sites (or their equivalent wherever GP happens to live) balked at their name.
    - atomwaffel 10y ago
      Yes, government services was what I thought of when I wrote "most websites". Then again, I feel like those tend to be the worst user experience offenders (which is what this comes down to). It took me several attempts to mangle my home address into something the US visa application form would accept and I've seen ö turned into Ã¶ on my own country's official websites so many times that I can remember the mojibake. I wonder why that is. I guess part of the reason is that government doesn't need to invest heavily in UX because there is no threat of people going elsewhere – when you need a passport, you need a passport, and you're not going to order one from Amazon. In fact, there's probably a disincentive for government to continuously improve their UX precisely because any changes to a system that's in place and working well enough only cost them money. Are there any counterexamples of government websites that offer good user experience?

raubitsj 10y ago

Good ole Little Bobby Tables https://xkcd.com/327/ https://xkcd.com/327/

wagglycocks 10y ago
I feel like that joke is so widely known that it can be retired, or at least, referenced without needing the link.

83457 10y ago

That reminds me of the approach taken by many contact forms for members of the US congress. I believe the vendors have to follow certain security processes which includes sending the user to a 404 page if certain malicious attempts are detected. The end result in many cases is that there is a list of words that can not be submitted successfully through their form that changes over time and just check in certain fields based apparently on however the vendor wants to implement the security measure. Is your name Walter? Sorry but you sound like a sql injection attack so we will redirect you and the message you just spent an hour on over to a 404 page.

icedchai 10y ago

Prepared statements are hard. They've only been around for decades.

greenleafjacob 10y ago

Prepared statements are actually worse than escaping in Postgres because prepared statements can sometimes use the wrong query plan. Refer to the documentation: > In some situations, the query plan produced for a prepared statement will be inferior to the query plan that would have been chosen if the statement had been submitted and executed normally. This is because when the statement is planned and the planner attempts to determine the optimal query plan, the actual values of any parameters specified in the statement are unavailable. PostgreSQL collects statistics on the distribution of data in the table, and can use constant values in a statement to make guesses about the likely result of executing the statement. Since this data is unavailable when planning prepared statements with parameters, the chosen plan might be suboptimal. Sometimes this can be more than "suboptimal" but actually an entirely different index and orders of magnitude worse performance.