CODEWITHSUNDEEP
c#sqlsql-server
Takarii
Takarii

Reputation: 1648

Avoiding SQL injection using a non parameterised query

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)

Upvotes: 4

Views: 108

Answers (2)

L Kefalas
L Kefalas

Reputation: 63

If the string was formated like that:

string fullstring = "DROP\tTABLE\tTableName";

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

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

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

Upvotes: 6

Related Questions