My name still causes SQL errors
twitter.com
twitter.com
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.
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 \%t Veld
This SQL literal string:
'Gijs in \'t Veld'
Gijs in 't Veld
From a security perspective there is nothing more "proper" about prepared queries than correctly escaped non-prepared queries.
>> SELECT 'here is an apostrophe: \'';
>> returns: here is an apostrophe: '
Gijs in \'t Veld
Gijs in 't Veld
'Gijs in \'t Veld'
Gijs in 't VeldOnly 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
query("INSERT INTO table VALUES (?1, ?2, ?3)", "a", "b", "c")
insert_into("table").values("a", "b", "c")
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.
Group myGroup = "Groupia"
Person[] myPersons = ["Foo", "Bar", "Baz"]
SELECT *
FROM dbo.Person
WHERE name IN ('Foo', 'Bar', 'Baz')
AND GroupName = 'Groupia'
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()
Example:
INSERT INTO USERS (name) VALUES ('Gijs in \'t Veld');
... | name | ...
========================
... | Gijs in 't Veld | ...
(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)
Gijs in 't Veld
'Gijs in \'t Veld'
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
return "'" + replace(str, "'", "\\'") + "'"
return "'" + replace(replace(str,"\\","\\\\"),"'","\\'") + "'"
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
SELECT * FROM MyTable WHERE Name IN ('foo', 'bar', 'baz')
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
SELECT * FROM MyTable WHERE NameType = 'foo_group';
INSERT INTO #T values -- [your array here]
SELECT * FROM MyTable WHERE Name IN (SELECT t from #t);
SELECT * FROM MyTable WHERE Name IN ?; -- vaporware SQL syntaxI 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:
('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.
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.
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.
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.
> 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.