Would removing spaces in a string protect against SQL injection?

Question

I was curious if it's possible to protect against an SQL injection attack by removing all spaces from an String input?

I have been reading up on SQL Injection at OWASP, but they don't mention anything about removing spaces, so I was curious why it would, or would not work?

---

I was looking at this question, which asks about trim, and the top answer says this:

No, adding a trim will not prevent sql injection. trim only removes space on the outside of your string.

Select * from aTable where name like '%'+@SearchString+'%'

If you @SearchString held something like

'' update aTable set someColumn='JackedUpValue' where someColumn like '

Then when you put it all together and execute it dynamically you would get

Select * from aTable where name like '%' update aTable set someColumn='JackedUpValue' where someColumn like '%'

---

However if you took that search string

update aTable set someColumn='JackedUpValue' where someColumn like 

and performed the operation shown in this question, wouldn't you get

updateaTablesetsomeColumn='JackedUpValue'wheresomeColumnlike

which should not execute, right?

I'm curious if there is any form of SQL injection that could defeat this? Are there one word dangerous commands? If this can be defeated, would removing spaces at least help a bit with defense?

Note: I'm asking this question purely out of curiosity, not as way to circumvent using the "proper" methods of SQL Injection Prevention.

Answer 1

No. Removing spaces would not prevent SQL injection, as there are many other ways to make the parser process your input. Lets look at an example. Imagine that you had a url which used user supplied input unsafely in a query:

http://example/index.php?id=1 => SELECT * from page where id = 1

In your example the attacker would use spaces:

http://example/index.php?id=1%20or%201=1 => SELECT * from page where id = 1 or 1=1.

Removing the spaces would collapse the injection into a string.

Now let's imagine that the attacker used another form of white space, i.e. tabs:

http://example/index.php?id=1%09or%091=1 => SELECT * from page where id = 1 or 1=1.

Removing the spaces would still allow the injection through.

Depending on the technology in use the attacker could replace the spaces with /**/ %00 %09 %0a %0d or any number of unicodes that causes tokenization by the SQL parser. While the referenced example removed more than just spaces, the aforementioned example takes advantage of SQL comments to cause the tokenization which are not whitespace. You would still be vulnerable.

The only reliable way to prevent SQL injection is to use parameterized queries.

Answer 2

We're in 2016! SQL injections are a thing of the past unless you use insecure code.

Whatever language you use, if you want to prevent any and all SQL injections, use prepared statements or any other type of data binding.

Prepared statements separate the query from the data, making it impossible for the data to affect the query.

To directly answer your question, removing spaces would reduce SQL injections (when using outdated code and libraries), but would surely limit your input text (no spaces anywhere).

Answer 3

It would limit the problem, but not eliminate it. Consider the following situation:

$un = str_replace(" ", "", $_POST["username"]);
$pw = hash($_POST["password"];
$sql = "SELECT * FROM users WHERE username = '$un' AND password = '$pw'";

Lets say I post the username admin'--. That would log me in as admin, without using a single space.

As wireghoul points out, you would need to remove other blank characters like tab as well. But as Julie Pelletier points out, just use prepared statements. Trying to come up with clever schemes to stop SQLi without it might be a fun game, but it will never give you the security that prepared statements does. Everything else is just a distraction.

Answer 4

No. Let's say you have this as your SQL:

"select * from people where last_name = '" + surname + "'"

If I enter: 'OR(1=1)OR'a'=' into the input it turns into:

select * from people where last_name = ''OR(1=1)OR'a'=''

Which executes in Oracle and MySQL (at least) and returns all the rows from the table.

Answer 5

However if you took that search string

update aTable set someColumn='JackedUpValue' where someColumn like and performed the operation shown in this question, wouldn't you get

updateaTablesetsomeColumn='JackedUpValue'wheresomeColumnlike which should not execute, right?

As others have pointed out there are other ways of getting whitespace into various SQL Database implementations which you would need to account for such as tabs and \n (newline). Almost every database engine would happily accept:

update
aTable
set
someColumn='JackedUpValue'

There may be various unicode characters that work as whitespace based on localization settings in various databases but you'd really have to dig into implementation details to figure that out. There's really no reason to try and cover all these edge cases for string replacement vs just using parameterized queries.

Answer 6

So here is an idea. Database servers take the incoming SQL query and run it through a parser resulting in a parse tree. Then they turn the tree into a plan and execute the plan.

The essence of injection is that the parser produces a tree different from the one intended by the programmer.

So the fix is to be able to detect unusual parse trees. Walk the tree after parsing and produce a string in canonical form minus the data values. Compute a SHA hash of the string. Keep a table of known hashes for the application/database user. Warn or abort if the server sees an unknown hash.

Obviously, there is a startup problem. So the programmer would have to run the application in a testing mode, extract the hashes after exhaustive testing, and the load the server with the hashes on application startup. Then turn on abort-on-new-hash and no more SQL inject should be possible.