Sql injection

Question

Sql injection

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16167 More actions · Answer 1

and taking in consideration that most of the injections is done using hex representations of the data it is quite easy to inject if not using parameters.
https://www.c-sharpcorner.com/article/sql-injection-with-hex-code-and-its-prevention-mechanism/

and this is indeed one of the most common methods of doing it - and unless you either limit the size of the string being concatenated or you ensure the string contains no hex sequences there is no way around it other than parameters.

andycadley SSCertifiable Points: 5296 More actions · Answer 2

Matt Miller (4) - Friday, November 30, 2018 5:14 PM
autoexcrement - Friday, November 30, 2018 2:37 PM
I'd love to see an example too. But it seems the issue is somewhat complicated by the use of C# and SQL. Hacking this system would require passing a value that tricks both the C# code and then the SQL code. So it's not something we can just test using SQL alone is it?
Actually - C# is the thing that gives you an opening, since it allows escape sequences.
Try passing this in:
userinput="Idontwantanyofyourdata\u2019 union select name from sys.tables;--";
Which would then concatenate into the following once it passes through C# into SQL.
SELECT Field FROM Table WHERE Filter='Idontwantanyofyourdata' union select name from sys.tables;--'
And then again - you could replace the UNION with just about anything. Delete/insert/select from system tables.

Whilst that might work if you've pasted that into the source code, I don't believe it does if you actually try to get a user to type it in directly - the compiler will handle escape sequences in source code but the actual language itself doesn't pay them any attention. And if the attacker can type directly into the source code, they don't actually need to inject anything at all.

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 3

andycadley - Saturday, December 1, 2018 11:01 AM
Matt Miller (4) - Friday, November 30, 2018 5:14 PM
autoexcrement - Friday, November 30, 2018 2:37 PM
I'd love to see an example too. But it seems the issue is somewhat complicated by the use of C# and SQL. Hacking this system would require passing a value that tricks both the C# code and then the SQL code. So it's not something we can just test using SQL alone is it?
Actually - C# is the thing that gives you an opening, since it allows escape sequences.
Try passing this in:
userinput="Idontwantanyofyourdata\u2019 union select name from sys.tables;--";
Which would then concatenate into the following once it passes through C# into SQL.
SELECT Field FROM Table WHERE Filter='Idontwantanyofyourdata' union select name from sys.tables;--'
And then again - you could replace the UNION with just about anything. Delete/insert/select from system tables.
Whilst that might work if you've pasted that into the source code, I don't believe it does if you actually try to get a user to type it in directly - the compiler will handle escape sequences in source code but the actual language itself doesn't pay them any attention. And if the attacker can type directly into the source code, they don't actually need to inject anything at all.

Yes, it does sound pretty odd behaviour from C#. Perhaps Matt could replicate it with a real example?
If I typed "Idontwantanyofyourdata\u2019 union select name from sys.tables;--" into a textbox then displayed what I'd typed I would expect to see \u2019 not a quote.
I just tried it with a few lines of code:
namespace WindowsFormsApp1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); }

private void Form1_Load(object sender, EventArgs e) { }

private void button1_Click(object sender, EventArgs e) { MessageBox.Show(textBox1.Text,"Result"); } } }
And got this:

So I couldn't replicate it.

andycadley SSCertifiable Points: 5296 More actions · Answer 4

The usual methods for getting around escaped quoting are things like:
1) Second-order SQL injection. When the passed in string gets passed through multiple layers and the inner code isn't taking into account the need to escape quotes.
2) When the inner code isn't expecting a string, but a numeric value. Consider a query being constructed like :
"SELECT * FROM table WHERE value > " + escapedUserInput
Replacing quotes will not help one bit here
3) Unicode exploits. If the SQL layer is using varchar and you pass certain Unicode characters through, they can get converted to single quotes - but that happens after your C# replace statement, so they don't get escaped where you expect them to.
4) Weird and wonderful behaviours of the SQL driver being used, particularly where it unexpectedly defaults to QUOTED IDENTIFIER=OFF
5) String truncation hacks. Doubling up single quotes will make strings longer and, in certain circumstances, SQL Server will silently truncate values passed to the database to a fixed length. So if there are multiple parameters being passed in (say first and last name) you may be able to exploit trunctation of one of them to force the second into an unescaped state.

Using parameterised queries can avoid pretty much every possible case of this (except maybe second-order injection in some cases) and will almost certainly perform better as well. It's just a no-brainer to do things properly rather than relying on hacky approaches.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 5

andycadley - Sunday, December 2, 2018 9:48 AM
Using parameterised queries can avoid pretty much every possible case of this (except maybe second-order injection in some cases) and will almost certainly perform better as well. It's just a no-brainer to do things properly rather than relying on hacky approaches.

I vehemently agree. With all of the publicized security breaches out there, it's a real wonder that anyone would still even remotely consider relying on such a hack for death by concatenation/string substitution.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Jonathan AC Roberts - Sunday, December 2, 2018 8:15 AM
andycadley - Saturday, December 1, 2018 11:01 AM
Matt Miller (4) - Friday, November 30, 2018 5:14 PM
autoexcrement - Friday, November 30, 2018 2:37 PM
I'd love to see an example too. But it seems the issue is somewhat complicated by the use of C# and SQL. Hacking this system would require passing a value that tricks both the C# code and then the SQL code. So it's not something we can just test using SQL alone is it?
Actually - C# is the thing that gives you an opening, since it allows escape sequences.
Try passing this in:
userinput="Idontwantanyofyourdata\u2019 union select name from sys.tables;--";
Which would then concatenate into the following once it passes through C# into SQL.
SELECT Field FROM Table WHERE Filter='Idontwantanyofyourdata' union select name from sys.tables;--'
And then again - you could replace the UNION with just about anything. Delete/insert/select from system tables.
Whilst that might work if you've pasted that into the source code, I don't believe it does if you actually try to get a user to type it in directly - the compiler will handle escape sequences in source code but the actual language itself doesn't pay them any attention. And if the attacker can type directly into the source code, they don't actually need to inject anything at all.
Yes, it does sound pretty odd behaviour from C#. Perhaps Matt could replicate it with a real example?
If I typed  "Idontwantanyofyourdata\u2019 union select name from sys.tables;--" into a textbox then displayed what I'd typed I would expect to see \u2019 not a quote.
I just tried it with a few lines of code:
namespace WindowsFormsApp1 { public partial class Form1 : Form {    public Form1()    {     InitializeComponent();    }
   private void Form1_Load(object sender, EventArgs e) { }
   private void button1_Click(object sender, EventArgs e)    {     MessageBox.Show(textBox1.Text,"Result");    } } }
And got this:

So I couldn't replicate it.

Considering we didn't actually talk about HOW the C# program gets its input, I didn't try to assume, so yes - I create it as a local variable and then ran the actual line of code as provided. It would make life a bit harder if the program were actually using proper parameters, but then again - all we had was a single line of code showing demonstrably bad behavior, so I wasn't going to assume pristine practices elsewhere 😛

As to sounding like odd behavior - as was mentioned before- using escaped characters of various kinds are usually the most common vectors for injection.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

As a follow up - it can happen from external input, assuming you're treating the user input as formatted text, or you were serializing in XML or JSON inputs. In those cases you'd often need to serialize and unescape the strings.

using System;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            string fun;
            fun = @"\u2019 union select name from sys.logins;--";
            while (fun!="")
{
                fun = System.Text.RegularExpressions.Regex.Unescape(fun);
            string sql;
            sql= "SELECT Field FROM Table WHERE Filter = '"+fun.Replace("'","''")     +"'";

                Console.WriteLine(sql.ToString());
                fun = @console.ReadLine();
            }

}
}
}

I left the local variable in, but you can paste in the SAME string from the input and it still has the same issue,
The point still remains: there are injection weaknesses from this approach. Yes - the challenge moves out a bit (how to get the t

As to any further "what ifs": perhaps the OP will volunteer his code

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?