6 ms·

Are you sure that’s what is happening at Reddit? You shouldn’t need to sanitise your inputs for SQL. Paramatised SQL has been a thing in some languages for two

Are you sure that’s what is happening at Reddit? You shouldn’t need to sanitise your inputs for SQL. Paramatised SQL has been a thing in some languages for two decades now. This really is a long solved problem by now. Output is a different matter though but that’s because of rendering content safely down to HTML, JavaScript or JSON (to name a few examples). SQL shouldn’t come into the equation by this point.

tie_ 7y ago
This. I'm tired of people implying or right out stating that SQL injection is an input validation problem. Why couldn't you have foo' OR 1=1; as a title of your post? It is all good characters as far as text entry is concerned. SQL injection is really a problem of how you pass parameters to your SQL layer. Parametrized queries are the (easy and widely available) solution. If you are concatenating input to your SQL queries, you're doing it wrong.
- squiggleblaz 7y ago
  How many people named O'Brien are told they can't sign up, or passwords get rejected because they contain special characters? It's crazy. Even if you're using 1990s technology without parameterised queries, it's not like it's impossible to say `insert into users (name, motto) values ('O\'Brien', 'foo\' OR 1=1;')`.
  - fwsgonzo 7y ago
    Yep. I've stopped counting the amount of websites that force me to use weak passwords. It's crazy that this is still a thing in 2020. I wish the controls on browsers came with a green V that implements best practices (8+ symbols, no filter) so that people who made websites understand that this is what they should conform to. Not their own misconceptions about password security.
    - lodovic 7y ago
      I wish websites would stop enforcing a "password policy". An insecure password should be a choice. If you are so sure you cannot secure your site, leave authentication to a third party provider. All this leads to is zillions of user accounts that are used only once.
    - hinkley 7y ago
      I’m sorry, your password must be 16 characters or less and contain no white space or punctuation.
  - chaz6 7y ago
    I have heard stories about people in Ireland struggling to get certain services because their name contains a Fada, but some of the identity paperwork they have is missing the Fada due to lack of support by computer systems.
- simias 7y ago
  I blame PHP. Many webdevs active today started with it, and the standard library's solution to injections was escaping everything half a dozen times just in case. Because PHP being PHP nobody saw any red flags when they implemented a function named "mysql_real_escape_string". Apparently they've deprecated these functions since then, but the damage is done.
  - deleted 7y ago
    [deleted]
  - ivanhoe 7y ago
    But that's not a thing for 15 years or more? PDO was added around 2005, and even before that anyone in their right mind used mysqli extension for prepared statements. Since 2012 you can't even use the mysql extension without getting a depreciation warning. And yes, in 90s php's security sucked, but that was nothing php specific, it was just the sentiment of that time. Everyone did it, in all languages. I remember using tons of $dbh->do() in Perl's DBI back then, intentionally avoiding to prepare statements for a quick and dirty stuff (and most of the scripts back then were quick and dirty stuff). It's in a big part because we were used to building desktop apps and thinking in terms of security that applied for them like being careful about your pointers and input strings lengths and stack overflows and stuff. Web was still pretty new thing.
    - teh_klev 7y ago
      > But that's not a thing for 15 years or more? PDO was added around 2005 Ex-shared hosting bod here, who had the joy of managing our PHP environments :( Sadly in the real world, even after the great big (and pointless) act of deprecating and removing the mysql_* library, naive developers (and experienced ones that should've know better) just moved onto mysqli_* or PDO and still used string concatenation with raw inputs, instead of learning how to parameterise their queries. Used to drive me flippin' nuts.
      - ivanhoe 7y ago
        > naive developers (and experienced ones that should've know better) just moved onto mysqli_* or PDO and still used string concatenation with raw inputs, instead of learning how to parameterise their queries. True, I stand corrected, I've just checked and Wordpress still does it just like that: https://github.com/WordPress/WordPress/blob/master/wp-includes/wp-db.php#L2023 https://github.com/WordPress/WordPress/blob/master/wp-includ...
jedberg 7y ago
When I said "sanitized input" I was simplifying parameterized SQL in the case of reddit. But since I said "for your datastore" I was being less specific since different datastores require different methods.
- laumars 7y ago
  Parametrised SQL isn’t sanitising your input. It’s injecting those values in at byte code so your values are separate to the query language. Calling that process “sanitisation” is, at best, highly misleading. Also the methods don’t really change across different SQL databases, at least not conceptually. Sure the RDBMS drivers might change but these days that stuff is usually abstracted away into a single framework for SQL. The real significant change would be switching to a NoSQL database but if you’re doing that then it’s not SQL you need to be “sanitising” anyway.
  - jedberg 7y ago
    > Calling that process “sanitisation” is, at best, highly misleading. That's a fair criticism. But that is what I've called it for a long time. > The real significant change would be switching to a NoSQL database but if you’re doing that then it’s not SQL you need to be “sanitising” anyway. Right, that's why I started off by saying "Sanitize your inputs so that it can be safely stored in your data store", so that it could apply to any data store. It's just a terminology argument at this point. But my main point was that you need to still think carefully about how you're going to store your data and do it safely, and then also make things safe on the way out, like the article suggests.
    - wccrawford 7y ago
      You don't "sanitize your inputs" for your datastore. You escape the outputs as you send it to your datastore. (Either through oldschool methods, or parameterization.) Sanitizing your input means changing them permanently. You don't actually want to do that. You want to store exactly what the original value was, but you want to do it safely. When you retrieve it again, it should be the same as it was originally. If you "sanitize" the input, you won't necessarily have the original value ever again.
mannykannot 7y ago
We should certainly use parameterization always, and this will allow us to save 'Bobby Tables' type input into our databases, but we should acknowledge that there is then the potential risk that some internal, probably non- public-facing program or script, either now or in the future, will contain a bug that leads to its execution. The spread of natural language processing into systems and analysis tools might increase the scope for this sort of thing.
- laumars 7y ago
  I agree you can never say never but you also can't sanitise against a risk that hasn't been defined yet simply because you wouldn't know what needs to be sanitised. For example what if your NLP is bootstrapped from a shell script and your database content has been stripped of SQL but still contains stuff that might be interpreted as $(sub-shells)? Before long you run into a situation where literally no characters are considered safe (eg even alpha characters in the English alphabet are used as tokens in some programming languages and "what if someone builds a script in one of those languages?"). The only sane way to address the unknown is to treat raw strings as "dirty" and follow best practices when handling them (plus all the usual processes to properly test your code before it's used in production). In which case you're back to no longer needing input sanitisation.
  - mannykannot 7y ago
    My point is not that input sanitization is a solution, but that parameterized database input is not the end of the issue, from a broader, whole-systems security point of view. Many data types have some concept of well-formedness, and in those cases, there are pragmatic reasons for only accepting well-formed input that go even beyond the security aspect.
    - laumars 7y ago
      I completely agree and I also said this in my original comment you replied to. This is why I got confused when you said "but we should acknowledge..." (ie thinking you were raising a point other than what myself and others had already acknowledged).
auiya 7y ago
> Are you sure that’s what is happening at Reddit? "I was the first (paid) employee of reddit" https://www.jedberg.net/ https://www.jedberg.net/ I mean...
- laumars 7y ago
  That doesn't mean the information supplied was accurate. Which, as it happened, it wasn't and this whole thing was really just a massive misunderstanding.

tie_ 7y ago

This. I'm tired of people implying or right out stating that SQL injection is an input validation problem. Why couldn't you have foo' OR 1=1; as a title of your post? It is all good characters as far as text entry is concerned. SQL injection is really a problem of how you pass parameters to your SQL layer. Parametrized queries are the (easy and widely available) solution. If you are concatenating input to your SQL queries, you're doing it wrong.

squiggleblaz 7y ago
How many people named O'Brien are told they can't sign up, or passwords get rejected because they contain special characters? It's crazy. Even if you're using 1990s technology without parameterised queries, it's not like it's impossible to say `insert into users (name, motto) values ('O\'Brien', 'foo\' OR 1=1;')`.
- fwsgonzo 7y ago
  Yep. I've stopped counting the amount of websites that force me to use weak passwords. It's crazy that this is still a thing in 2020. I wish the controls on browsers came with a green V that implements best practices (8+ symbols, no filter) so that people who made websites understand that this is what they should conform to. Not their own misconceptions about password security.
  - lodovic 7y ago
    I wish websites would stop enforcing a "password policy". An insecure password should be a choice. If you are so sure you cannot secure your site, leave authentication to a third party provider. All this leads to is zillions of user accounts that are used only once.
  - hinkley 7y ago
    I’m sorry, your password must be 16 characters or less and contain no white space or punctuation.
- chaz6 7y ago
  I have heard stories about people in Ireland struggling to get certain services because their name contains a Fada, but some of the identity paperwork they have is missing the Fada due to lack of support by computer systems.
simias 7y ago
I blame PHP. Many webdevs active today started with it, and the standard library's solution to injections was escaping everything half a dozen times just in case. Because PHP being PHP nobody saw any red flags when they implemented a function named "mysql_real_escape_string". Apparently they've deprecated these functions since then, but the damage is done.
- deleted 7y ago
  [deleted]
- ivanhoe 7y ago
  But that's not a thing for 15 years or more? PDO was added around 2005, and even before that anyone in their right mind used mysqli extension for prepared statements. Since 2012 you can't even use the mysql extension without getting a depreciation warning. And yes, in 90s php's security sucked, but that was nothing php specific, it was just the sentiment of that time. Everyone did it, in all languages. I remember using tons of $dbh->do() in Perl's DBI back then, intentionally avoiding to prepare statements for a quick and dirty stuff (and most of the scripts back then were quick and dirty stuff). It's in a big part because we were used to building desktop apps and thinking in terms of security that applied for them like being careful about your pointers and input strings lengths and stack overflows and stuff. Web was still pretty new thing.
  - teh_klev 7y ago
    > But that's not a thing for 15 years or more? PDO was added around 2005 Ex-shared hosting bod here, who had the joy of managing our PHP environments :( Sadly in the real world, even after the great big (and pointless) act of deprecating and removing the mysql_* library, naive developers (and experienced ones that should've know better) just moved onto mysqli_* or PDO and still used string concatenation with raw inputs, instead of learning how to parameterise their queries. Used to drive me flippin' nuts.
    - ivanhoe 7y ago
      > naive developers (and experienced ones that should've know better) just moved onto mysqli_* or PDO and still used string concatenation with raw inputs, instead of learning how to parameterise their queries. True, I stand corrected, I've just checked and Wordpress still does it just like that: https://github.com/WordPress/WordPress/blob/master/wp-includes/wp-db.php#L2023 https://github.com/WordPress/WordPress/blob/master/wp-includ...

jedberg 7y ago

When I said "sanitized input" I was simplifying parameterized SQL in the case of reddit. But since I said "for your datastore" I was being less specific since different datastores require different methods.

laumars 7y ago
Parametrised SQL isn’t sanitising your input. It’s injecting those values in at byte code so your values are separate to the query language. Calling that process “sanitisation” is, at best, highly misleading. Also the methods don’t really change across different SQL databases, at least not conceptually. Sure the RDBMS drivers might change but these days that stuff is usually abstracted away into a single framework for SQL. The real significant change would be switching to a NoSQL database but if you’re doing that then it’s not SQL you need to be “sanitising” anyway.
- jedberg 7y ago
  > Calling that process “sanitisation” is, at best, highly misleading. That's a fair criticism. But that is what I've called it for a long time. > The real significant change would be switching to a NoSQL database but if you’re doing that then it’s not SQL you need to be “sanitising” anyway. Right, that's why I started off by saying "Sanitize your inputs so that it can be safely stored in your data store", so that it could apply to any data store. It's just a terminology argument at this point. But my main point was that you need to still think carefully about how you're going to store your data and do it safely, and then also make things safe on the way out, like the article suggests.
  - wccrawford 7y ago
    You don't "sanitize your inputs" for your datastore. You escape the outputs as you send it to your datastore. (Either through oldschool methods, or parameterization.) Sanitizing your input means changing them permanently. You don't actually want to do that. You want to store exactly what the original value was, but you want to do it safely. When you retrieve it again, it should be the same as it was originally. If you "sanitize" the input, you won't necessarily have the original value ever again.

mannykannot 7y ago

We should certainly use parameterization always, and this will allow us to save 'Bobby Tables' type input into our databases, but we should acknowledge that there is then the potential risk that some internal, probably non- public-facing program or script, either now or in the future, will contain a bug that leads to its execution. The spread of natural language processing into systems and analysis tools might increase the scope for this sort of thing.

laumars 7y ago
I agree you can never say never but you also can't sanitise against a risk that hasn't been defined yet simply because you wouldn't know what needs to be sanitised. For example what if your NLP is bootstrapped from a shell script and your database content has been stripped of SQL but still contains stuff that might be interpreted as $(sub-shells)? Before long you run into a situation where literally no characters are considered safe (eg even alpha characters in the English alphabet are used as tokens in some programming languages and "what if someone builds a script in one of those languages?"). The only sane way to address the unknown is to treat raw strings as "dirty" and follow best practices when handling them (plus all the usual processes to properly test your code before it's used in production). In which case you're back to no longer needing input sanitisation.
- mannykannot 7y ago
  My point is not that input sanitization is a solution, but that parameterized database input is not the end of the issue, from a broader, whole-systems security point of view. Many data types have some concept of well-formedness, and in those cases, there are pragmatic reasons for only accepting well-formed input that go even beyond the security aspect.
  - laumars 7y ago
    I completely agree and I also said this in my original comment you replied to. This is why I got confused when you said "but we should acknowledge..." (ie thinking you were raising a point other than what myself and others had already acknowledged).

auiya 7y ago

> Are you sure that’s what is happening at Reddit? "I was the first (paid) employee of reddit" https://www.jedberg.net/ https://www.jedberg.net/ I mean...

laumars 7y ago
That doesn't mean the information supplied was accurate. Which, as it happened, it wasn't and this whole thing was really just a massive misunderstanding.