1

I am taking a text input from the user, then converting it into 2 character length strings (2-Grams)

For example

RX480 becomes

"rx","x4","48","80"

Now if I directly query server like below can they somehow make SQL injection?

select * 
from myTable 
where myVariable in ('rx', 'x4', '48', '80')
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Furkan Gözükara
  • 22,964
  • 77
  • 205
  • 342
  • I can't imagine that you will end up with any sql injection that will cause problems in your server, but you still might get funky results and an error without proper escaping/sanitizing should someone enter `';foo` into your form. – JNevill Jun 28 '16 at 20:50
  • @JNevill ty for answer. ';foo would become "';",";f","fo","oo". so i guess it is not possible? what do you think? – Furkan Gözükara Jun 28 '16 at 20:52
  • 2
    You are now using double quotes instead of single quotes like your example sql. I was thinking that would be converted to `select * from myTable where myVariable in ('';',';f','fo','oo')` which is three statements. Statement 1: `select * from myTable where myVariable in ('';` and statement 2: `',';` and statement 3: `f','fo','oo')` All of which are nonsense. – JNevill Jun 28 '16 at 20:53
  • This seems like a very SLOW and painful way to avoid just using parameterized queries. – Joel Coehoorn Jun 28 '16 at 21:10
  • @JoelCoehoorn it's difficult to parameterize `in` clauses. You have to have a parameter per set-member, so you usually have to dynamically generate them. – Blorgbeard Jun 28 '16 at 21:12
  • @Blorgbeard you are right. i would have to be go that way for 100% being sure but i still wonder 2 character transformation can still hold any invulnerability :) – Furkan Gözükara Jun 29 '16 at 11:11

4 Answers4

2

SQL injection is not a matter of length of anything.

It happens when someone adds code to your existing query. They do this by sending in the malicious extra code as a form submission (or something). When your SQL code executes, it doesn't realize that there are more than one thing to do. It just executes what it's told.

You could start with a simple query like:

select * 
from thisTable 
where something=$something

So you could end up with a query that looks like:

select * 
from thisTable 
where something=; DROP TABLE employees;

This is an odd example. But it does more or less show why it's dangerous. The first query will fail, but who cares? The second one will actually work. And if you have a table named "employees", well, you don't anymore.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
durbnpoisn
  • 4,666
  • 2
  • 16
  • 30
  • 1
    But... in OP's case they are taking the user input and semi-sanitizing it by converting to 2-character length strings, so they aren't going to be able to drop in a second statement, at least in any way that make sense... that I can tell. – JNevill Jun 28 '16 at 20:49
  • 1
    @JNevill you are right. i am asking specifically 2 gram transformed input. – Furkan Gözükara Jun 28 '16 at 20:50
  • @JNevill : Okay... I can see that now. But doesn't that seem like a lot of extra trouble to go through when there are things already in place to prevent those sorts of shenanigans? – durbnpoisn Jun 28 '16 at 20:52
  • Agreed. I would suggest, at a minimum, escaping single quotes in the 2-gram'd user input, or just dropping them completely from the user input before converting to 2-gram, depending on the needs. But worst case scenario (I believe) is that you end up with multiple statements, all of which error out for being in error or nonsense. – JNevill Jun 28 '16 at 20:58
1

Two characters in this case are sufficient to make an error in query and possibly reveal some information about it. For example try to use string ')480 and watch how your application will behave.

zajonc
  • 1,935
  • 5
  • 20
  • 25
0

Although not much of an answer, this really doesn't fit in a comment.

Your code scans a table checking to see if a column value matches any pair of consecutive characters from a user supplied string. Expressed in another way:

declare @SearchString as VarChar(10) = 'Voot';
select Buffer, case
  when DataLength( Buffer ) != 2 then 0 -- NB: Len() right trims.
  when PatIndex( '%' + Buffer + '%', @SearchString ) != 0 then 1
  else 0 end as Match
  from ( values
    ( 'vo' ), ( 'go' ), ( 'n ' ), ( 'po' ), ( 'et' ), ( 'ry' ),
    ( 'oo' ) ) as Samples( Buffer );

In this case you could simply pass the value of @SearchString as a parameter and avoid the issue of the IN clause.

Alternatively, the character pairs could be passed as a table parameter and used with IN: where Buffer in ( select CharacterPair from @CharacterPairs ).

As far as SQL injection goes, limiting the text to character pairs does preclude adding complete statements. It does, as others have noted, allow for corrupting the query and causing it to fail. That, in my mind, constitutes a problem.

I'm still trying to imagine a use-case for this rather odd pattern matching. It won't match a column value longer (or shorter) than two characters against a search string.

HABO
  • 15,314
  • 5
  • 39
  • 57
0

There definitely should be a canonical answer to all these innumerable "if I have [some special kind of data treatment] will be my query still vulnerable?" questions.

First of all you should ask yourself - why you are looking to buy yourself such an indulgence? What is the reason? Why do you want add an exception to your data processing? Why separate your data into the sheep and the goats, telling yourself "this data is "safe", I won't process it properly and that data is unsafe, I'll have to do something?

The only reason why such a question could even appear is your application architecture. Or, rather, lack of architecture. Because only in spaghetti code, where user input is added directly to the query, such a question can be ever occur. Otherwise, your database layer should be able to process any kind of data, being totally ignorant of its nature, origin or alleged "safety".

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345