I know some developers double up apostrophes to mitigate SQLi. (This is when the input is ' so it becomes '')
Is there a way to beat this?
This is on MS SQl Server.
Information Security Stack Exchange is a question and answer site for information security professionals. It only takes a minute to sign up.
Sign up to join this communityI know some developers double up apostrophes to mitigate SQLi. (This is when the input is ' so it becomes '')
Is there a way to beat this?
This is on MS SQl Server.
I try to show both sides of the security spectrum. Security is important, so you shouldn't just know how to defeat security, you should know how to implement it as well. Thus, I'm going to list a prevention first. If you don't want to read this, scroll down.
Doubling up apostrophes is not the answer when it comes to security, and it can lead to insecurity. The answer depends on the programming language you're using.
Note that these are all the same concepts in every language. They just have different names.
Even with prepared/callable statements or parameterized queries, the following is incorrect:
// Bad code, don't use
string sqlString = "SELECT * FROM [table] WHERE [col] = '"+ something +"' AND [col2] = @Param";
You must never concatenate your SQL variables.
With SQL server, depending on your chosen language of choice, your query should look something like this:
C#
using (SqlCommand command = new ("SELECT * FROM [tab] WHERE LName = @LName", connection))
{
// Add new SqlParameter to the command.
command.Parameters.Add(new SqlParameter("LName", txtBox.Test));
// Read in the SELECT results.
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
}
}
Java
Connection con = DriverManager.getConnection("database-connection-string","name","pass");
// Question marks are the bound variables which are parsed in the defined column order.
PreparedStatement findLName = con.prepareStatement("SELECT * FROM [tab] WHERE LName = ?");
// The order in which the question marks appear. You can have more than one in a prepared statement. First one is "1", second is "2", and so on.
findLName.setString(1, Lastname);
findLName.executeQuery();
Statement stmt = con.createStatement();
PHP
Check w3schools for an example, I don't want my answer getting too long.
Note that this still doesn't get rid of potential injection of scripts onto a web page. You could insert exactly as they ask, within acceptable ranges, and then output the results to HTML.
If they insert the following (dumbed-down example):
<script> window.location.href='hxxp://www.mymalwarewebsite.com/'; </script>
...and you output that result to your page, then you're in big trouble. So what if you remove anything from <script>
to </script>
? What if they insert:
<scri<script>pt> window.location.href='hxxp://www.mymalwarewebsite.com/'; </scri<script>pt>
...? If you remove the script tags with replace, you're still stuck with that exploit.
What you really want is the following in addition to the above:
HtmlEncode
Java Html Sanitizer from OWASP
HtmlEntities
.Ā
could be converted to A
. Even worse: U+02BC
, or ʼ
would be translated as '
, which is U+0027
. This is called Unicode-based Smuggling.
StringEscapeUtils
is ancient and rather rubbish. If you really want to escape HTML properly, you need to use a proper HTML sanitiser such as the one from OWASP.
Dec 18, 2015 at 15:22
UpdateUser(int)
, if this isn't properly handled on the server side, you could modify the request to update any id. That means you can install yourself as an admin if you know the admin's userid. From there, you can likely access things most users aren't allowed to, and cause a lot of trouble. Likewise, being able to inject a drive-by-download on all of your website visitors could enable you to steal far more information than is stored in the database.
Dec 18, 2015 at 17:08
Is there a way to beat this?
Maybe (at least with MySQL). And it's really simple (you don't need any special encodings or the absence of quotes, or anything):
\' [injection] -- -
So for example, you have this query:
SELECT FROM table WHERE user = '[INPUT]';
Input is \' [injection] -- -
, but with '
doubled, it becomes: \'' [injection] -- -
, which leads us to this query:
SELECT FROM table WHERE user = '\'' [injection] -- -';
The injected part will be executed as a query, as it's not inside quotes anymore.
The only proper defense against SQL injection are prepared statements. They are also not hard to use, and often result in nicer code. There is really no good excuse for homemade defenses such as doubling quotes.
// update: It seems that this would not actually be possible with MSSQL. So that would leave the two issues already mentioned in the other answers: It's still vulnerable if no quotes are used in the query, or (possibly) for certain character sets.
It also depends on how the doubling actually takes place. If it's eg done via the database, it may be vulnerable.
This only prevents SQL injection attacks via string parameters. Sometimes, the parameter is an integer (think http://www.example.org/?page=3
), then you don't need an apostrophe. Sometimes you can check if the parameters vulnerable if you replace page=3
with page=1+2
, for example.