How Parametize?

[VBAHole22]

I know why I should parametize my queries to prevent SQL injection attacks. But HOW does ADO.NET go about doing this? Does it use a regular expression and weed out bad characters in the parameter? Does it only let parametized queries run data access language?

Does it just reject parameters that it considers bad? Is there an error code with this? Will it cleanse input parameters and try to run them? Is this an open specification that anyone can find more info about or is it a closely guarded MS secret? Are there ways to write your own parameter checking routines? :)

[Afraits]

Note None of the following may be remotely close but it seems logical,

I believe that by using parameters you are effectively preventing any access to the underlying SQL statement. If you use

SQL="Select * from mytable where(Colcondition=" & variable & ")"

can be modified by appropriate use of variable to

SQL="Select * from mytable where (Colcondition=1) go Delete * from importanttable go PRINT getdate()"

Effectively creating a completely different SQL statement with serious consequences.

 

The use of parameters means that the underlying SQL statement is locked/compiled and the parameters are passed in without any risk of the statement being adjusted.

ie in the above example mytable.colcondition would be searched for values equal to "1) go Delete * from importanttable go PRINT getdate("

 

I don't think it weeds out characters or does any other kind of validation beyond checking that parameter supplied is of the datatype expected or implicitably convertable to said datatype.

 

That said however none of the above may actually be true but in my mind it makes sense and thats how I see it.

 

The other reason for parameterised queries is that they can be stored as encrypted stored procedures - further protecting the code & DB.

[HJB417]

I created a data client for mysql that uses named parameters. I use regex to make sure all the named parameters are present, and the minimum numbers of parameters needed for the query to succeed exist. As for oledb.net and odbc.net, they just invoke other code that does this, so if I used the mysql odbc driver, it would call that code and that code could do the parameter validation. I don't know how the sql client works though but it's all decompilable for the curious.

[VBAHole22]

Afraits,

 

I'm afraid I don't follow your logic. Part of the reason I posted this question is because everytime I asked someone they would give me a response like you did. It's circular thinking. You are saying that it works becuase that is the way it does it.

If you have a variable then nothing is 'compiled/locked'

Even if the statement were not 'adjusted' it would still just pass along what the user put in on the form, right?

 

As far as I can see there are 3 ways it could work:

either it cleans input

or it rejects input

or it runs the query in a sandbox (no DAL)

[HJB417]

This is all done on the client/driver. It's all implementation base. The .NET runtime does no parameter or SQL verification.

ODBC.NET and OLEDB.NET internally will load the driver speicified in the connection string. It's that driver that does the parmeter/SQL verification, if it chooses to.

 

The MS SQL Client is different, as it's an included databse client/driver. Since it's in .NET code, you can decompile it to see if it does parameter/SQL verification on the client side. I'm pretty sure it's done on by the server even if the client has already done this.

 

The concept here is intention and implementation:

intention: using parameters to execute sql.

implementation: there is no standard or single way to implement the intention. It varies from database client to database client.

[VBAHole22]

Really still doesn't answer the question. How does MS handle the parameters for the SqlClient? Is it rejecting bad characters like a regular expression or is it running in a sandbox?

[HJB417]

I don't know how the sqlclient does it.

[Afraits]

To my mind neither SQLserver nor the SQLClient can possibly perform any validation on parameters beyond checking they are the correct datatype because other than that any value might be a valid search criteria. Any parameter validation is the responsiblity of the programmer building the SQLcommand, for which regular expressions would be an ideal way to check for possible injection attacks.

 

I'm going to experiment a bit further to investigate to see if parameterised queries do stop SQL injection attacks on text parameters but as such attacks require text strings to be passed to the query using parameterised queries will prevent attacks based on numeric or date fields with no further work. If you want to know how they do the datatype checking I don't know.

[VBAHole22]

Cool. I would be very interested in the results.

 

I thought about the case of a login page for a website. What if the user decided she wanted to user 'Delete *' as her password. Or '*&Thg%7' as her username. Would these be allowed?

[Afraits]

Thats what I mean about parameters not being able to be validated by ADO.NET or a dataprovider beyond the datatype check there are just too many variations for it to be practical.

 

Plus there is no reason why someone couldn't have a username or password like the ones you mention. The only way to stop it in my understanding is for such things to be validated at input before they are passed to ADO or the underlying data providers and databases, and custome messages such as "Your selected username includes control characters - please restrict your username to alpha numeric characters and not symbols such as @ & or %.

 

Been landed with awkward data processing job so might be a few days before I can report back on experiments.

[VBAHole22]

I agree. This lends more credence to the sandbox theory. If SqlClient let your parameters pass through unfettered BUT only allow a select query with them then you could have any username you want. But what about Insert queries being parmeterized?

[Afraits]

To my mind the same principles apply - technically anything could be a valid text parameter,value to be inserted/updated or searched on and so a parameterised INSERT (or DELETE or UPDATE) applies the same as a SELECT query.

[VBAHole22]

If you google sql injection in .NET you get some interesting results. I check the top ten or so results and in most cases they had a checklist of things to stop injection. Parameterization didn't even make the list on some of these sites!

The top stopgap methods were:

 

1. Restrict authorization on the SQL user commiting the query

2. Sanitize the user input for single quotes (at least, if not more)

[Rick_Fla]

Read this site, might help out.

 

http://winfx.msdn.microsoft.com/library/default.asp?url=/library/en-us/wd_adonet/html/33e09965-61d5-48cc-9e8c-3b047cc4f194.asp

 

basic idea, SQL treates the parameter as it would one in a stored procedure. it knows it is a value and not a command to run. Thus making it so the injected command is not ran by the SQL server but treated as a value to say, a wehere clause. Hope this helped.

Edited April 25, 2005 by Rick_Fla

[penfold69]

One of the most important functions that parameterisation handles is the proper escaping of control characters within parameters.

 

Take the example of a text string containing a single quote. Without parameterisation, an SQL statement could end up syntactically incorrect, due to the inclusion of a single quote charater, or even worse an SQL injection attack could be present.

 

Using parameters would escape the single quote character (with a leading \ in the case of SQL server, I believe) so that it is interpreted an a literal string within the statement.

 

Without parameters:

 

Select * from MyTable where MyField = 'Bob's House'

 

With parameters:

 

Select * from MyTable where MyField = 'Bob\'s House'

 

This *is* handled by the SQLClient, so that any strings sent to the server are 'sane' in terms of input.

 

B.

[VBAHole22]

I'm not disagreeing with you, but I have seen websites that instruct users to use regular expressions AND parameterize, which would debunk your theory.

 

I think I need to step up the testing to get to the bottom of this.

[Rick_Fla]

Most sites I have read says you should, and it does not state using or not using parameters, is to do a replace on ' with '' so that it helps prevent SQL injection. But others sites I have read state what I said above, sql knows not to run the parameter as a SQl command.

 

Have fun testing and please post your results.

[penfold69]

Be my guest and test away.

 

I'm basing my statements on prior knowledge, and also the internals of the MySql.Data library, which is a fully managed, open source library for accessing MySQL Databases. The beauty of open source is the ease at which you can inspect the source and see exactly what it does in these cases, and in this case, I know I'm correct ;)

 

B.

[HJB417]

I don't think the sqlclient modifies the values of parameters. In most of the database api's I've seen, the query is sent to the server, and then the parameters are then sent to the server and then a command is sent to toe server to tell the server to process/execute the sql with the parameters. I don't know how it's done by reggie's c# mysql data provider (i don't use it), but this is how its done using the mysql c api, which I use.