52

I was reading about SQL injection and saw this, which got me thinking:

input fields as small as possible to reduce the likelihood of a hacker being able to squeeze SQL code into the field without it being truncated (which usually leads to a T-SQL syntax error).

Source: Microsoft SQL Server 2008 R2 Unleashed

What is the is the shortest field size where SQL injection can cause harm?

With harm being database modification or returning results not intended by design. Including an end comment marker (--) in a two character field would not cause harm, it would just cause a failed query. The potential hacker, might learn the field is susceptible to injection, but they are unable to leverage it.

  • 13
    SQL Injection isn't always done on an input field - you can target orderby clauses in URLs for example where you could execute malicious sql. The limited size of a field isn't going to stop that. – iainpb Feb 28 '17 at 16:25
  • 17
    @iain: Good point. Also, limiting field lengths for (I assume) received HTML form data doesn't strike me as a good way to avoid sql injection. SQL injection avoidance is basically a solved problem; just let the database connection library handle it by using prepared statements with placeholders, instead of pasting together queries using your programming language's string functions and getting it wrong because you forgot to protect against loophole number 59654. – Out of Band Feb 28 '17 at 16:39
  • 114
    Am I the only one which thinks that book should be publicly burned? Parameterized queries have been around for so long that the author leads me to believe that they don't have even half a clue what they are talking about. It sounds like they copy+pasted garbage from a collection of blogs and released a book. They might as well tell you that you should practice getting shot with smaller bullets so that you can build up immunity to larger bullets. – MonkeyZeus Feb 28 '17 at 19:21
  • 50
    How about 0 chars? And only if validated on the server. – immibis Feb 28 '17 at 21:47
  • 8
    @Xander I'd say that "look elsewhere" is more noble than brazenly claiming something like "Obfuscate your form data using JavaScript and a random key generated by the server before sending data to your server." At least that e-commerce book is saying "Hey guy, we don't want your server to get ducked with a sandpaper phallus so if you care about security then get a good book about it. Our book just teaches you how build an e-commerce site, not how to secure one." – MonkeyZeus Mar 1 '17 at 14:09
86

No, there is no length that is too short to be exploitable (at least in some situations).

A length-filter is not a valid protection against SQL injection, and prepared statements really are the only proper defense.

A length filter is however a good measure as defense in depth (as are integer filters, alphanum filters, etc). There are many situations where e.g. valid input could never be above say 30 characters, but where meaningful exploitation requires more. It should (but probably doesn't) go without saying that any filtering as defense in depth must be taking place server-side as anything client-side can simply be bypassed.

Restriction Bypass

Restriction clauses (e.g. AND/OR) can be bypassed by two characters, which can cause real harm, not just a failed query. The most simple example is a login (other examples would be the unauthorized deletion of additional data):

SELECT * FROM users WHERE userid = [id] AND password = [password]

Injection:

id = 1#
password = wrong_password

Payload: 2 chars

DoS

DoS attacks require very few characters. In a MySQL example, it takes 7 for the actual call + x for the given seconds + whatever is needed to be able to call the function and fix the query.

Example:

SELECT * FROM users WHERE userid = [id]

Injection (this is a valid injection, a longer form would be 1 AND sleep(99)):

sleep(99)

Payload: 9 chars

Reading Data

If the data is displayed, the length depends mainly on the table and column name. I'll assume equal column count for all tables (it may happen, and it saves characters).

Example:

SELECT * FROM comments WHERE commentid = [id]

Injection:

1 union select * from users

Payload: 27 chars.

Editing Data

Unauthorized database modifications can also be achieved with few characters.

Example:

UPDATE users SET password = '[password]' WHERE id = [id]

Injection (into password):

',isadmin='1

Payload: 12 chars

A restriction bypass would also work (the result is that all passwords are now empty*):

'#

Payload: 2 chars

* The password example is used for simplicity; passwords should be hashed making the example impossible. The example still applies in all similar situations (updating a username, updating permissions, and so on)

  • 44
    A length-filter is not a valid protection against SQL injection, and prepared statements really are the only proper defense. This is the only valid answer. SQL without Prepared Statements remind me of "Strategies how to ride a dead horse" – Alexander Mar 1 '17 at 2:41
  • Good luck preparing a statement with a variable number of placeholders, such as the right side of operator IN. – Damian Yerrick Mar 3 '17 at 19:58
  • @DamianYerrick I think that's a flawed hypothetical. Why would you need to provide an end user endless combinations of placeholders for an IN clause? It's easy enough to have a handful of templates that allow 1..n (for a small n). – Shawn C Mar 3 '17 at 22:33
  • In "SELECT * FROM users WHERE userid = [id]" you can inject a if a is the name of a column, so then you have an injection of only 1 character. – Paul Mar 3 '17 at 23:17
  • @ShawnC The use case is that a user enters a list of items to be queried. For one thing, how large is "a small n"? It'd seem to violate the zero-one-infinity rule. For another, if there are two different list-valued input fields, you now need to store and test O(n^2) templates. – Damian Yerrick Mar 3 '17 at 23:43
90

Without context, I'm going to presume that the author is referring to input fields in a client application. The easiest example to use would be a form in an HTML page in a web application, though what I'm about to describe is effective for virtually any type of client application.

Given this context, the answer is no, there is no maximum input field length short enough to protect you from SQL injection, because you don't control the maximum input field length. The attacker does. When the form containing the input field lives on the client (like in a browser window on the attacker's machine) it is outside of the trust boundary, and out of your control. It can be manipulated in any way the attacker wants or needs, or avoided completely. Remember, for a web app, all you get is an HTTP request. You have no idea how it was created, and no reason to believe that anything you asked the browser to do actually happened, that any client-side security controls were actually run, that anything in the request is as you intended or in any way safe.

So no, input field length is not a valid SQL injection prevention mechanism, and never, never trust input that crosses a security boundary without validating it.

  • 15
    @hmrojas.p It doesn't though, because an input filed max length doesn't actually prevent a malicious user from sending as much data as he or she wants in that field. I can set an input maximum length to 3, and an attacker could still send or 1=1;drop table users;-- If you don't check on the server side, there's nothing preventing this. – Xander Feb 28 '17 at 18:46
  • 2
    Yeah, I agree, I was assuming that there was a length validation on server side, I mean that kind of SQL Injection ( or 1=1 ) could affect a SQL query like UPDATE or DELETE, it could break the data integrity, even if you have a lenght validation on server side, this measure is complementary. – hmrojas.p Feb 28 '17 at 18:51
  • 29
    I downvoted because this assumes that the field length is not validated server side. While I agree that any answer should mention that the length has to be validated server side and outside the control of the user to be of any use, I believe that server side validation of length would cmpletely invalidate your entire answer. (The answer might still be no, but not for this reason.) – jpmc26 Mar 1 '17 at 0:51
  • 3
    @jpmc26 Whether it is validated server-side is irrelevant. The context of the quote is a client-side "security" measure, and server side validation is not addressed. – Xander Mar 1 '17 at 0:53
  • 25
    @Xander The only person who has assumed client-side-only validation here is you. I see nothing in the question or comments that suggests this is the situation the OP is describing. – jpmc26 Mar 1 '17 at 0:54
11

input fields as small as possible to reduce the likelihood of a hacker being able to squeeze SQL code into the field without it being truncated (which usually leads to a T-SQL syntax error).

IMHO, this is just shit. Using the length of input fields to protect from SQL injection is terrible advice:

  • The only correct way to protect from SQL injection is the use of a prepared statement. The input data is a parameter of the query and will never be used as SQL text.
  • The size of a field must be controlled server side because you can never trust what comes in a request - it might be a forged one - and should be used to sanitize data before usage in the business layer or database storage.
  • Advice to use anything other than the best practices is confusing for novice developers.
8

No.

Consider the query:

select * from tweets
WHERE RowNum >= [offset]
AND RowNum < [offset] + [limit]

If you could inject a single non-integer (for example, the letter "a") for offset, the query would result in a syntax error and no posts would show up yielding your "results not intended by design" requirement for causing damage. If you can inject an empty string you get the same effect with a zero length payload. Injecting an end comment would be a waste of two characters ;)

An attacker can leverage this in a denial of service attack (differently from a network flooding generally associated with DoS, yet still a DoS). Depending on the service being denied, this could be catastrophic.

As other answers suggest field length has no bearing on the SQL injection impact. Prepared, parameterized statements are the way to go as mentioned in the OWASP SQL Injection Prevention Cheat Sheet.

  • Denial of service and SQL injection are two very different things. This answer does not offer anything helpful that is not already addressed in existing answers. – James Jenkins Feb 28 '17 at 23:42
  • 2
    Correct, SQL injection is one form of the "injection attack" category, that can yield many different ends including denial of service. Denial of service is a technique can be achieved through different vectors. A common one is via a distributed flood. But any technique that yields an unresponsive service is properly classified a denial of service attack. This can include SQL injection. My answer provides an explanation of how this can be done with zero characters. It also provides a link to the OWASP SQL injection Prevention Cheat Sheet. Thanks for pointing out that I should highlight those. – benrifkah Mar 1 '17 at 4:50
4

No.

Simple example of a one-character SQL injection:

`

It would throw an error which would be an example of "returning results not intended by design". Many times errors can be used to gain information that will help later in an exploit.

  • 4
    Perhaps you could add an explanation on how that injection could be harmful? – Anders Mar 2 '17 at 12:01
  • It would an error which would be an example of returning results not intended by design Many times errors can be used to gain information that will help later in an exploit. – MikeSchem Mar 2 '17 at 15:29
  • 2
    Thanks! I took the liberty of including your explanation in the answer. – Anders Mar 2 '17 at 18:21
3

Yes.

The maximum length is zero. The only way to make untrusted input safe via maximum length, with no other validation or checks, is to completely disregard the input starting at index 0.

There are many other (better) answers here, but this one serves to answer the theoretical question of how to stay safe when field length is the only tool at your disposal.

  • I'm honestly not convinced that a clever enough attacker couldn't manage an attack with zero characters. – Devsman Mar 3 '17 at 21:44
  • Along the lines of other comments above -- where any results not intended by design is some form of SQL injection; would it also be valid to say that any behavior not intended by design is a form of Sql injection (referring to the sleep(60) example above .. which was kind of cool :)? A 0 length SQL injection then, would not necessarily have to involve any SQL.... as long as you could tie up the connection?? – Charles Thomas Mar 4 '17 at 17:42
  • @Dan well jeez if I knew that I'd go ahead and make a countermeasure. This is the problem with security in general... We think it's secure until some attacker proves it isn't. – Devsman Mar 6 '17 at 13:33
0

The length of a field won't have anything to do with SQL injection, because such an attack works by simply commenting out previous code and starting the attack code, so the length of any field won't effect this strategy.

  • I think OP's point is that the application is not going to allow an arbitrary amount of text to reach the database-- it still truncates at a particular length. – Jedi Mar 3 '17 at 4:51
0

There is a lot of theory here but no actual examples. When I found a real-world sql injection vulnerability (now patched) I tried using input fields that were around 30 characters long. This was very frustrating as it took me a while to figure out that everything after 30 characters was dropped, and 30 characters did not leave me room for much.

(a lot of sql injection examples have simple sql "like select * from table where id = @id", real world sql will usually be much more complicated)

You can get fancy with your input, and a sql expert could probably use some tricks to reduce the payload count. But no matter how tricky you are if you want to select some long table name you're going to need a lot of characters for that.

Anyways, when I switched to using a input field with over 1k characters I found that plenty for sql injection.

That being said, like many commenters above me said, The best defense against sql injection is prepared statements

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.