Avoiding SQL injection using a non parameterised query

Question

Im aware of how parameterised queries work, and ive used them in every non hardcoded query I've written so far, however when writing a function to create a dynamic query (for testing purposes) it made me question whether it would actually be safe to use "as is"

string sql = "SELECT * FROM Table WHERE";

string fullstring = "The quick brown fox jumped over";
string[] words = fullstring.Split(' ');

foreach (string item in words)
{
    sql = sql + " Column LIKE '%" + item + "%' AND";
}

sql = sql.Remove(sql.Length - 3);

If I were to turn this into a query, the result would be

SELECT * FROM Table WHERE Column LIKE '%the%' AND Column LIKE '%quick%' AND Column LIKE '%brown%' AND Column LIKE '%fox%' AND Column LIKE '%jumped%' AND Column LIKE '%over%'

Now i'm still pretty sure that this is still open to injection attacks due to the lack of parameters, however i'm unsure how due to the delimiter being a space character making things like SELECT * FROM TABLE or DROP TABLE unable to be written in the string as each would be split into their own strings ie. SELECT,*,FROMand TABLE

Can anyone enlighten me further?

(Note, not planning on using this as an alternative to proper parameters, just trying to understand)

Damien_The_Unbeliever · Accepted Answer · 2016-02-15 09:06:14Z

6

select"name"from"sys"."columns"

Is an example of a query I can write that SQL Server will process and that contains no spaces.

So, just say no.

Here's another example showing another way of bypassing "no spaces" and in an "injected" form:

select name from sys.columns where name like '%a'union/**/all/**/select/**/name/**/from/**/sys.objects

edited Feb 15, 2016 at 9:06

answered Feb 15, 2016 at 9:02

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Takarii Over a year ago

Fantastic example of what i was looking for. Thank you very much.

Zohar Peled Over a year ago

Also, splitting a string on ' ' does not remove tabs nor does it removes line breaks, so...

Damien_The_Unbeliever Over a year ago

And you can also just use /**/ to separate tokens also

Zohar Peled Over a year ago

Or add the following to the end of the sentence: %';DROP TABLE table;-- (note that the spaces are actually tabs, and they won't be filtered out by your split.

L Kefalas · Accepted Answer · 2016-02-15 09:09:15Z

1

If the string was formated like that:

string fullstring = "DROP\tTABLE\tTableName";

you would still have a problem with injection... Just a simple example.

answered Feb 15, 2016 at 9:09

L Kefalas

631 silver badge9 bronze badges

Collectives™ on Stack Overflow

Avoiding SQL injection using a non parameterised query

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related