Can I be vulnerable to SQL injection by adding no-space input to my query?

Question

I take a string from user input and break it into a space (using \ w) into an array of strings. Then I go through the array and add part of the where clause, like this:

query += " AND ( " + "field1 LIKE '%" + searchStrings[i] +"%' " + " OR field2 LIKE '%" + searchStrings[i] +"%' " + " OR field3 LIKE '%" + searchStrings[i] +"%' " + ") "; 

It seems to me that this is dangerous since I am adding user input to my request. However, I know that there are no spaces in any search string, since I split the initial input into spaces.

Can this be attacked with SQL injection? Granted by Robert');DROP TABLE students;-- in fact, nothing would lose, since there should be empty space. In this example, he will not behave correctly, but there will be no damage.

Can someone with a lot of experience fighting SQL injection help me either fix this or calm down?

Thanks!

EDIT:

Wow, this is a big contribution. Thanks to all who responded. I will explore full-text search and, at a minimum, parameterize my query.

Just to better understand the problem, would it be possible to embed if all single quotes of spaces And were excluded?

Answer 1

Every time you allow a user to enter data in a query string like this, you are vulnerable to SQL injection and should be avoided like a plague!

You have to be very careful so you can populate the searchStrings [] array. You should always add variable data to your query using parameter objects:

 + field1 like @PropertyVal Or field2 like @PropertyVal Or field3 like @PropertyVal etc... 

And if you use SQL Server, for example

 Query.Parameters.Add(new SqlParameter("PropertyVal", '%' + searchStrings[i] + '%')); 

Be very careful how you create the query string that you intend to run on the production server, especially if it has any data that has consequences!

In your example, you mentioned Little Bobby Tables

Robert '), students of DROP TABLE, -

And quoted it because he needed empty space, you couldn't do it, but if the attacker encoded it using something like this:

 Robert');Exec(Replace('Drop_Table_students','_',Char(32)));-- 

I would say it is better to be safe and do it right. There is no easy way to make sure that you catch every scenario otherwise ...

Answer 2

Yes, this script did not contain spaces, just encoded characters that SQL decoded and executed: http://www.f-secure.com/weblog/archives/00001427.html

The script you entered was something like this:

DECLARE% 20% @S 20NVARCHAR (4000); SET% 20 @S = CAST (0x440045004300 4C00410052004500200040005400200076006100720063006800610072 00280032003500350029002C0040004300200076006100720063006800 610072002800320035003500290020004400450043004C004100520045 0020005400610062006C0065005F0043007500720073006F0072002000 43005500520053004F005200200046004F0052002000730065006C0065 0063007400200061002E006E0061006D0065002C0062002E006E006100 6D0065002000660072006F006D0020007300790073006F0062006A0065 00630074007300200061002C0073007900730063006F006C0075006D00 6E00730020006200200077006800650072006500200061002E00690064 003D0062002E0069006400200061006E006400200061002E0078007400 7900700065003D00270075002700200061006E0064002000280062002E 00780074007900700065003D003900390020006F007200200062002E00 780074007900700065003D003300350020006 ...

What SQL code is decoded for:

DECLARE @T varchar (255) '@C varchar (255) DECLARE Table_Cursor
CURSOR To select a.name'b.name from sysobjects a'syscolumns b, where a.id = b.id and a.xtype = 'u' and (b.xtype = 99 or b.xtype = 35 or b .. .

etc., so it can be done with any table in the database without any spaces.

Answer 3

There are too many ways to do it wrong that I won’t rely on anyone to tell me “no, it will be safe because ...”

How to avoid escaping a space in some form (URL encoding or somethign). How about using unobvious Unicode space characters that your simple tests don't check. What if your database supports some malicious operations that do not require a space?

Make the right way: use PreparedStatement (or whatever your platform uses for parameterization with injection safety), add and add “%” to user input and use this as a parameter.

Answer 4

Rule of thumb: if the row you are adding is not SQL, it must be escaped using prepared statements or the correct escape function from your DB client library.

Answer 5

I completely agree that query parameters are an absolutely safe way. With them, you have no risk of introducing SQL injections (unless you are doing something stupid with parameters), and there is no overhead for escaping.

If your DBMS does not support query parameters, it MUST support string escaping. In the worst case scenario, you can try to avoid single quotes yourself, although there is still a Unicode exploit that can get around this. However, if your DBMS does not support query parameters, it probably also does not support Unicode. :)

Added: Also. as you wrote, there are killers for performance - no indexes can be used. I would suggest finding full-text indexing capabilities for your DBMS. They are designed specifically for such cases.

Answer 6

Yes, they can still enter elements, without spaces, which may not greatly affect, but they are still a vulnerability.

In general, blindly adding user input to a query is not a good idea.

Answer 7

Here's a trivial injection, if I set field1 to this, I have listed all the rows in your database. This may be bad for security ...

 '+field1+' 

You should use parameters (they are also valid for embedded SQL), for example

 AND Field1 = @Field1