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.
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.
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?
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...
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.
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.
RDF on the not-so-cheap? Real-time profiling of the internal Factory code by keeping track of every attribute and running a bunch of exciting INSERT hooks?
I have seen this pattern before, it comes when somebody is sick of having to adjust their database schema every time somebody comes up with a new user attribute that they absolutelydefinitely need to keep audited. Now you can add whatever "columns" you like, boss!
I had to think about the database design of ProcessWire (PHP CMS).
It uses one table for identifiers and field tables for all data connected to the identifier.
To store a user with a username, a password and an email address it uses 3 tables. One for the username (identifier), one for the password and one for the email address.
The only safe way to insert a user is by using transactions.
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
"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.
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
I'm not advocating for escape characters instead of proper prepared queries, but in that case you would "simply" need to un-escape the name before showing it.
No, you don't need to unescape it when displaying it.
This SQL literal string:
'Gijs in \'t Veld'
Is a representation for these ASCII bytes/content:
Gijs in 't Veld
In other words. The backslash will not be stored as part of the string into the database. It's just a hint to the SQL parser that the following single quote should be interpreted as a literal single quote char and not as the end-of-string-character.
From a security perspective there is nothing more "proper" about prepared queries than correctly escaped non-prepared queries.
I can't tell if you're trolling or not, but this is not how string escaping works in SQL or any other programming language that I know.
>> SELECT 'here is an apostrophe: \'';
>> returns: here is an apostrophe: '
The backslash is not part of the string but just a hint to the compiler. The literal string '\'' represents a one byte (if ASCII) string containing only a single apostrophe character.
I meant that if you were to "sanitize" all input your program gets by replacing "'" by "\'" in there everywhere, then assuming that most of the rest of your program actually works as normal and calls libraries that do things correctly etc, you're just going to have backslashes show up because they're in the string. Maybe you also echo the string back to HTML immediately before it ever goes into SQL, et cetera.
Only escaping quotes that are inserted directly into SQL query strings is not what I understood by "sanitizing all input".
To me this is a failing of popular SQL APIs that don't provide a good way to escape characters and force you to either deal with horribly clumsy prepared statement APIs or just concatenate strings together.
My point is that every SQL API allows that as a fallback simply because they can't stop it. So basically, if your proper, official, "this is how you should do it" API sucks, users will fallback to mashing strings together.
Doubly true for dynamic SQL, for those people who are "everything must be done in procs" types.
SQL Servers have an API for a client language, and often they are extremely cumbersome for prepared statements. Not to mention that in many cases this "language" has no coherent way to say the obvious operation of "Escape all the control characters in this string".
It gets painful when the SQL gets complicated, like if you're assembling the query dynamically, or if you need to pass in an array-like object. For example, imagine if you're using a multi-select GUI object as a filter. How do you load in the selected items to filter the result set?
tl;dr: Do all the things you should be doing under the moniker of "sanitize inputs", but just don't call it sanitizing inputs because I'm very literal and it bugs me. See also: 'hacker' v. 'cracker'.
> I wonder how everyone in the original thread comments is still falling for this infamous "sanitize input!" trap.
I don't know, but what I do know is that plenty of people still don't
understand how to use SQL APIs for their languages. Perl's DBI has variable
placeholders (similar to %x sequences in printf()) for dozen years already.
Python's DB-API 2.0 (PEP 249) has placeholders, too. Heck, even PHP has those.
And placeholders make any input sanitization unnecessary, because it's
the library that does the escaping part of constructing a query. And this way
is more convenient, too, as string concatenation rarely results in pretty
code.
> And placeholders make any input sanitization unnecessary, because it's the library that does the escaping part of constructing a query.
I quietly hope that they don't send concatenated query full of escapes, but some binary encoding of the query structure followed by serialized data parameters.
Otherwise there would be a whole business of exploiting those escapers. And wasted bandwidth.
According to how DBI, DBAPI2, and PDO are designed, it's database driver's job
to add escaping as appropriate (if not using binary encoding; but this is
driver's call, too), so there's not much to exploit. Remember that you don't
only quote single-quotes with a backslash, you also need to quote backslashes
in raw input, too. Only the bandwidth could be of any concern, but I don't
remember any database with a text-based communication protocol.
There is much to exploit if the driver itself is buggy.
Theoretically it's possible that between various 8bit/16bit encodings, locales, collations, client/server configuration mismatches somebody could find a creative way to smuggle some unescaped quotemark.
Binary protocol which parses SQL commands client-side and treats strings as blobs of bytes removes any chance of SQL injection for good.
Okay, here's some pseudocode that drives me nuts in every one of these SQL APIs.
I have a Group and a list of names of persons.
Group myGroup = "Groupia"
Person[] myPersons = ["Foo", "Bar", "Baz"]
I want to use that to generate the following query:
SELECT *
FROM dbo.Person
WHERE name IN ('Foo', 'Bar', 'Baz')
AND GroupName = 'Groupia'
Every language that tries to do the printf-style approach sucks for this. In many of these cases these are OOP languages. How hard is it to provide an API that gives me concatenation-like semantics?
It would be easy to design an API to allow putting the parameter calculation with concatenation-like semantics but without the problems of concatenation. For example (using Pythonic multi-line strings):
query = new query()
query.Text = """
FROM dbo.Person
WHERE name IN (""" + query.ParamList(myPersons) + """)
AND GroupName = """ + query.Param(MyGroup) + """
"""
resultSet = Query.Execute()
or somesuch nonsense - easier still if you're using a language that has good support for string interpolation. No worrying about lining up your parameters with their placeholders in the string which is functionally illegible in a long query. Good, legible programming involves keeping things that are related close to each other, so good SQL generation would logically involve having parameter's meaning as close as possible to their usage. printf-like syntaxes fail at this.
This is false. Of course you can use any special characters in a literal string without using prepared statements. You just have to escape them correctly.
Example:
INSERT INTO USERS (name) VALUES ('Gijs in \'t Veld');
Inserts the following row into the database
... | name | ...
========================
... | Gijs in 't Veld | ...
Prepared statements have nothing to do with this and are _in no way_ more resilient to SQL injection vulnerabilities than correctly interpolated non-prepared SQL queries.
(In fact, some databases implement prepared statements as simple query string interpolation in the client/driver. So this might be exactly what's happening when you use 'prepared statements' depending on the db)
Is your method for doing so guaranteed to always produce a valid SQL string literal which represents the supplied string? Even if the user supplied string contains, for example:
>> How are you turning the user supplied string into the SQL string literal string? Is your method for doing so guaranteed to always produce a valid SQL string literal which represents the supplied string?
Yes. This is trivial. Any self-respecting junior programmer should be able to write this routine.
With respect, most junior programmers, whether self respecting or otherwise, would have trouble even recognizing that there is a need to cover some of those cases I mentioned, let alone successfully accommodating them.
Most likely you'll get a
return "'" + replace(str, "'", "\\'") + "'"
which only after the first bug report gets turned into a
Again, my problem is that the APIs for parameterized prepared statements in SQL servers are often ludicrously awful which leads programmers to throw up their hands for non-trivial SQL queries and just say "screw it, I'll just concatenate strings together".
Which could have easily been avoided if the APIs weren't crap, or if the API had exposed a method to say "escape all special characters in this string because it's getting concatenated into a query".
I mean, I can't count the DB/client-language APIs where writing a query that involves an IN clause with a provided array so that ["foo", "bar", "baz"]
became
SELECT * FROM MyTable WHERE Name IN ('foo', 'bar', 'baz')
without writing a non-trivial amount of fiddly code to handle building a list of parameters.
That's an obvious use-case, but writing it in a parametric format is a gigantic PITA so developers just concat strings. It's not right but it's the expected outcome of a bad interface that drives people away from the best practices.
I have to agree with your assessment of SQL APIs. Funny how not a single one seems to have been designed by someone who'd seen printf & scanf.
In their defense, your obvious use case is evidence of a database design problem and shouldn't be in the API. The thing `Name` is in should itself have a name or type or class or group or something, capturing the reason foo, bar, and baz go together. Then the SQL is
SELECT * FROM MyTable WHERE NameType = 'foo_group';
For the ad hoc alternative, you need an easy way to insert your array into a temporary table (again, API deficiency) or supply a table parameter, so you can do
INSERT INTO #T values -- [your array here]
SELECT * FROM MyTable WHERE Name IN (SELECT t from #t);
or
SELECT * FROM MyTable WHERE Name IN ?; -- vaporware SQL syntax
You might be interested to know your example doesn't escape that correctly. SQL syntax escapes quotes by doubling them:
('Gijs in ''t Veld')
Correctness is not as easy as falling out of bed.
> Prepared statements have nothing to do with this and are _in no way_ more resilient to SQL injection vulnerabilities than correctly interpolated non-prepared SQL queries.
Actually, that's why prepared statements have everything to do with it: correct escaping is error prone. The simple advantage of prepared statements is that the parameters are handled as data, not as syntax. There's nothing to invent, and nothing to go wrong.
> some databases implement prepared statements as simple query string interpolation
Do you have one in mind? I can name 10 that don't, all names you'd recognize.
Besides, you've hoisted yourself on your own petard. If, ad arguendum, correct interpolation is _in no way_ more vulnerable than prepared statements, and prepared statements are correct interpolation, how exactly are prepared statements worse?
>> You might be interested to know your example doesn't escape that correctly.
It does -- You're right that the double-quote escaping is part of the original SQL standard, while the c-style escaping is an extension to it. However so is a lot of behaviour in modern SQL databases and it doesn't make my example incorrect.
Off the top of my head, here is an incomplete list of databases that implement c-style string escaping: Mysql, Postgres, Vertica, BigQuery, Oracle 9.2
>> some databases implement prepared statements as simple query string interpolation -- Do you have one in mind?
Yes, immediately both mongodb and bigquery come to my mind which are both pretty popular and do not currently support server-side prepared statements. If you google for jdbc drivers for theses databases some will implement 'prepared queries' as string interpolation on the client side.
That's not what I said. My point (and I think we agree here) was that using a correct interpolation routine is just as secure as using a prepared statement with regards to SQL injection vectors.
Sanitizing input it's not just "let's replace these weird characters". That's what crappy programmers do when they adopt the "can't fail a request" mentality of certain paradigms (I'm looking at you PHP.)
When modifying data using some secret criteria and doing the unexpected is better than showing a validation error, all bets are off.
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.
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?
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.
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.
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?
The tax e-filing system in Massachusetts is a government website that offers a quite good user experience. It could be a bit better (e.g. it could save your general W-2 info from year to year on the assumption that you probably didn't change employers), but it's fairly nice in most ways; the W-2 thing is the only problem I've run into with it, and it has a fairly clear UI. Especially given the complexity of the actual paper MA tax forms.
On the other hand, the MA website where you pay things like unemployment insurance for your employees is terrible, complete with being offline outside of normal business hours (because afaict they take it offline while they do batch imports from physical media that companies send them). Note that this is not as much of a "voter-facing" website, of course. ;)
The DMV in Massachusetts can't handle a space in a last name. They convert it to a dash instead.
As a result, I know several people who have different names on their passport and their driver's license, simply because the computer system at the DMV is broken.
Actually, most 'injection' issues happen because of poor programming practices (not sanitizing input, not escaping SQL parameters, etc.) and not because databases don't support Unicode characters. It's not the OP or the grandparent's fault for not having a "nice-to-shitty-programs" name.
Sure, but poor programming practices come down to cost like anything else - whether they're due to low-quality programmers, lack of training, poor management or whatever. No-one is deliberately choosing to make their site worse.
True. Hiring better programmers is more expensive, but in the case of vulnerabilities the long-term cost of not doing the right thing are probably higher than the upfront cost of hiring someone more qualified.
The original tweet, for example, reeked of crappy hand-crafted PHP code. Pretty much any web framework, even PHP ones, wrap bare exceptions like that into a proper 500 error with no information about the underlaying system. How long will it take for someone to put two and two together (MariaDB, no escaping of user input, a users table of some sort) and come up with a SQL injection that exposes all the password hashes for that website?
> How much extra are you willing to pay to be able to use those symbols?
It shouldn't cost anything extra to use those symbols in a name. In fact, it should be cheaper, because they won't have to do checks on the input to determine if it's "valid". Just store and display the name using UTF-8 and be done with it.
No, sites don't have any obligation to serve everyone, but in the same way, potential customers have every right to criticize the site and it's poor implementation.
Oh please, that's ridiculous. It is not "extra work" to handle all the legal characters in your input, and it is also not "extra work" to handle errors server side and not blindly render them to the page. This is just amateur hour.
Well, to some limit. I run into chinese form all the time that doesn't accept more than 10 characters.
PS. Thank you for reposting my comment comment in an earlier thread [0], but it's not that I've been flagged, I've been hellbanned [1]. This is how "hacker" news threats it contributing members with another perspective. And yes, I do know what I'm talking about, because I'm in China. But no, not Chinese. Enjoy the timewasting.
So nobody should think that today "Ñ" is a shorthand.
-----------
(out of topic, just for moderators: hello mods, dang, why in the world is the user "uola" "hellbanned"? just because he posted too many responses? maybe he should have been informed to slow down, I know you have that feature. Is the reason is not to let know the "bots" or malicious users when the point of "too much" starts? Please ignore my question if it's inconvenient to answer, I just hope uola gets the chance to comment from some point in time later as I haven't seen anything bad in the dead comments.)
To me it's the same as using "vowel+e" to indicate umlaut on a vowel. It's an alternative transcription. In the US we can spell Müller as Müller, Mueller or Muller. All of them are correct and accepted. We understand the tradeoffs.
To be sorta on topic, I lived in many countries and I had to get used to people mangling my name in the most creative ways. As long as there is some form of ID or customer reference, I don't care.
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.
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.
83 comments
[ 3.4 ms ] story [ 126 ms ] threadConversely, 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.
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?
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...
I have seen this pattern before, it comes when somebody is sick of having to adjust their database schema every time somebody comes up with a new user attribute that they absolutely definitely need to keep audited. Now you can add whatever "columns" you like, boss!
Certain authentication methods for RADIUS require a cleartext password, sadly, which will be stored in the Cleartext-Password attribute.
It uses one table for identifiers and field tables for all data connected to the identifier. To store a user with a username, a password and an email address it uses 3 tables. One for the username (identifier), one for the password and one for the email address.
The only safe way to insert a user is by using transactions.
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.
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
This SQL literal string:
Is a representation for these ASCII bytes/content: In other words. The backslash will not be stored as part of the string into the database. It's just a hint to the SQL parser that the following single quote should be interpreted as a literal single quote char and not as the end-of-string-character.From a security perspective there is nothing more "proper" about prepared queries than correctly escaped non-prepared queries.
https://en.wikipedia.org/wiki/Escape_character
Only escaping quotes that are inserted directly into SQL query strings is not what I understood by "sanitizing all input".
Doubly true for dynamic SQL, for those people who are "everything must be done in procs" types.
It doesn't care how you came up with your statements. If you concatenate strings, that's your private business.
If you don't offer that, your language sucks.
They describe how to send SQL to the server, but they are not SQL and the SQL is not the API. Different layer.
Every DB API I've ever used has looked like
or something more like neither if which seem horrible or clumsy to me.I don't know, but what I do know is that plenty of people still don't understand how to use SQL APIs for their languages. Perl's DBI has variable placeholders (similar to %x sequences in printf()) for dozen years already. Python's DB-API 2.0 (PEP 249) has placeholders, too. Heck, even PHP has those. And placeholders make any input sanitization unnecessary, because it's the library that does the escaping part of constructing a query. And this way is more convenient, too, as string concatenation rarely results in pretty code.
I quietly hope that they don't send concatenated query full of escapes, but some binary encoding of the query structure followed by serialized data parameters.
Otherwise there would be a whole business of exploiting those escapers. And wasted bandwidth.
Huh? MySQL at least you can telnet into and query with a text protocol, no?
Theoretically it's possible that between various 8bit/16bit encodings, locales, collations, client/server configuration mismatches somebody could find a creative way to smuggle some unescaped quotemark.
Binary protocol which parses SQL commands client-side and treats strings as blobs of bytes removes any chance of SQL injection for good.
I have a Group and a list of names of persons.
I want to use that to generate the following query: Every language that tries to do the printf-style approach sucks for this. In many of these cases these are OOP languages. How hard is it to provide an API that gives me concatenation-like semantics?It would be easy to design an API to allow putting the parameter calculation with concatenation-like semantics but without the problems of concatenation. For example (using Pythonic multi-line strings):
or somesuch nonsense - easier still if you're using a language that has good support for string interpolation. No worrying about lining up your parameters with their placeholders in the string which is functionally illegible in a long query. Good, legible programming involves keeping things that are related close to each other, so good SQL generation would logically involve having parameter's meaning as close as possible to their usage. printf-like syntaxes fail at this.Example:
Inserts the following row into the database Prepared statements have nothing to do with this and are _in no way_ more resilient to SQL injection vulnerabilities than correctly interpolated non-prepared SQL queries.(In fact, some databases implement prepared statements as simple query string interpolation in the client/driver. So this might be exactly what's happening when you use 'prepared statements' depending on the db)
Is your method for doing so guaranteed to always produce a valid SQL string literal which represents the supplied string? Even if the user supplied string contains, for example:
* control characters * NULs * non-ASCII characters * Unicode combining characters * surrogate pairs
...?
Wouldn't it be nice not to have to worry about that?
Good. Then use a mechanism that lets you send the parameters as data outside of your SQL, such as parameterized prepared statements.
Yes. This is trivial. Any self-respecting junior programmer should be able to write this routine.
Most likely you'll get a
which only after the first bug report gets turned into a and that will have been arrived at after a lot of trial and error and incorrect numbers of backslashes.And if you think that is robust, you're likely in for a surprise when someone sends you some unicode data.
Which could have easily been avoided if the APIs weren't crap, or if the API had exposed a method to say "escape all special characters in this string because it's getting concatenated into a query".
I mean, I can't count the DB/client-language APIs where writing a query that involves an IN clause with a provided array so that ["foo", "bar", "baz"]
became
without writing a non-trivial amount of fiddly code to handle building a list of parameters.That's an obvious use-case, but writing it in a parametric format is a gigantic PITA so developers just concat strings. It's not right but it's the expected outcome of a bad interface that drives people away from the best practices.
In their defense, your obvious use case is evidence of a database design problem and shouldn't be in the API. The thing `Name` is in should itself have a name or type or class or group or something, capturing the reason foo, bar, and baz go together. Then the SQL is
For the ad hoc alternative, you need an easy way to insert your array into a temporary table (again, API deficiency) or supply a table parameter, so you can do orI mean, look at all this boilerplate:
http://stackoverflow.com/questions/10409576/pass-table-value...
You might be interested to know your example doesn't escape that correctly. SQL syntax escapes quotes by doubling them:
Correctness is not as easy as falling out of bed.> Prepared statements have nothing to do with this and are _in no way_ more resilient to SQL injection vulnerabilities than correctly interpolated non-prepared SQL queries.
Actually, that's why prepared statements have everything to do with it: correct escaping is error prone. The simple advantage of prepared statements is that the parameters are handled as data, not as syntax. There's nothing to invent, and nothing to go wrong.
> some databases implement prepared statements as simple query string interpolation
Do you have one in mind? I can name 10 that don't, all names you'd recognize.
Besides, you've hoisted yourself on your own petard. If, ad arguendum, correct interpolation is _in no way_ more vulnerable than prepared statements, and prepared statements are correct interpolation, how exactly are prepared statements worse?
It does -- You're right that the double-quote escaping is part of the original SQL standard, while the c-style escaping is an extension to it. However so is a lot of behaviour in modern SQL databases and it doesn't make my example incorrect.
Off the top of my head, here is an incomplete list of databases that implement c-style string escaping: Mysql, Postgres, Vertica, BigQuery, Oracle 9.2
>> some databases implement prepared statements as simple query string interpolation -- Do you have one in mind?
Yes, immediately both mongodb and bigquery come to my mind which are both pretty popular and do not currently support server-side prepared statements. If you google for jdbc drivers for theses databases some will implement 'prepared queries' as string interpolation on the client side.
Random Example: https://github.com/jonathanswenson/starschema-bigquery-jdbc/...
>> how exactly are prepared statements worse?
That's not what I said. My point (and I think we agree here) was that using a correct interpolation routine is just as secure as using a prepared statement with regards to SQL injection vectors.
When modifying data using some secret criteria and doing the unexpected is better than showing a validation error, all bets are off.
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.
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.
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.
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?
On the other hand, the MA website where you pay things like unemployment insurance for your employees is terrible, complete with being offline outside of normal business hours (because afaict they take it offline while they do batch imports from physical media that companies send them). Note that this is not as much of a "voter-facing" website, of course. ;)
As a result, I know several people who have different names on their passport and their driver's license, simply because the computer system at the DMV is broken.
The original tweet, for example, reeked of crappy hand-crafted PHP code. Pretty much any web framework, even PHP ones, wrap bare exceptions like that into a proper 500 error with no information about the underlaying system. How long will it take for someone to put two and two together (MariaDB, no escaping of user input, a users table of some sort) and come up with a SQL injection that exposes all the password hashes for that website?
It shouldn't cost anything extra to use those symbols in a name. In fact, it should be cheaper, because they won't have to do checks on the input to determine if it's "valid". Just store and display the name using UTF-8 and be done with it.
No, sites don't have any obligation to serve everyone, but in the same way, potential customers have every right to criticize the site and it's poor implementation.
Just if your language doesn't use it it doesn't mean it's still "just a shorthand."
PS. Thank you for reposting my comment comment in an earlier thread [0], but it's not that I've been flagged, I've been hellbanned [1]. This is how "hacker" news threats it contributing members with another perspective. And yes, I do know what I'm talking about, because I'm in China. But no, not Chinese. Enjoy the timewasting.
[0] https://news.ycombinator.com/item?id=11675395 [1] Turn showdead on, visit https://news.ycombinator.com/user?id=uola
I don't believe ASCII or UTF preceded the creation of the written language.
Ñ existed long before USA, ASCII and UTF.
So nobody should think that today "Ñ" is a shorthand.
-----------
(out of topic, just for moderators: hello mods, dang, why in the world is the user "uola" "hellbanned"? just because he posted too many responses? maybe he should have been informed to slow down, I know you have that feature. Is the reason is not to let know the "bots" or malicious users when the point of "too much" starts? Please ignore my question if it's inconvenient to answer, I just hope uola gets the chance to comment from some point in time later as I haven't seen anything bad in the dead comments.)
To be sorta on topic, I lived in many countries and I had to get used to people mangling my name in the most creative ways. As long as there is some form of ID or customer reference, I don't care.
> 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.