SQL Injection: A Tour

[dilettante]

Yes this is a database topic, but it is also meant as more of a developer discussion.

Many people seem to either be blithely unaware of the hazards or think they can simply escape quotes in dynamic SQL to avoid the problem instead of using parameterized queries.

It takes some time to read through it but I think it is educational:

SQL Injection Attacks by Example (http://www.unixwiz.net/techtips/sql-injection.html)

What do you do to prevent SQL injection problems (both malicious and accidental)?

[dee-u]

Parameterized queries and SProcs.

[Pino]

I never use inline sql anymore.

nice article, was a good read

[dilettante]

I think people who leave themselves open to SQL injection either don't know about it or maybe just don't have enough examples of how to avoid it.

As the article describes, most people who try to address the problem start out by using inline queries they used to allow quotes and/or apostrophes in text values. Since that's the most fundamental way to try an attack there's a belief that this solves the problem - until they look at it deeper.

Maybe there are already some FAQs here dealing with the issue, I need to take another look. I think more examples, or "template code" for people to follow, might help get more people to deal with it in a more sophisticated manner.

When I stumbled over that article I linked I was amazed at how many tricky things can be done to attack applications and databases.

[si_the_geek]

I've seen hundreds of cases of people not understanding (and sometimes even not caring) about protecting against injection, and it definitely seems to be a lack of education - usually of the complexity of the issue rather than the code to solve it.
Maybe there are already some FAQs here dealing with the issue, I need to take another look.Not yet, but that is the next article (or rather 3! [general theory, ADODB example, ADO.Net example]) that I am writing.

All we have at the moment is an example of parameters for ADODB, along with comparison to recordsets and inline SQL (in "how do I add a record?").

As always, I'm happy if anyone has any comments/additions/etc for any of the FAQ articles, or wants to write a new one (but if writing a new one, it is best to check with me first - as I generally know if anyone is working on something already, and what kind of topics are most needed).

[dilettante]

Not yet, but that is the next article (or rather 3! [general theory, ADODB example, ADO.Net example]) that I am writing.
These should prove valuable additions here.

I always like to see information that helps VB programmers avoid the bad habits often pointed to when people heap scorn on the community.

[Pino]

These should prove valuable additions here.

I always like to see information that helps VB programmers avoid the bad habits often pointed to when people heap scorn on the community.

Or any programmers :) this sint really Vb specific

[si_the_geek]

Absolutely, I've seen many threads in the C#/PHP/... forums where the same issue is apparent - I don't know how to deal with the syntax for those languages, but hopefully a decent explanation of why you should use parameters will be enough to get people to find the syntax for their language.

[dilettante]

Or any programmers :) this sint really Vb specific
True enough, but I often see this sort of thing pointed at those using VB, fair or not. Information is power, and providing it to VBers is a good thing.

Absolutely, I've seen many threads in the C#/PHP/... forums where the same issue is apparent - I don't know how to deal with the syntax for those languages, but hopefully a decent explanation of why you should use parameters will be enough to get people to find the syntax for their language.
Well I know some people seem to think C# programmers are more 'leet, 1337, or 31337 though a large number are former VB programmers. I'd lump them in together and would never dream of shorting them on good information.

Solid syntax examples covering a range of options would be very helpful to people. Just looking at ADO there are several ways to pass parameters to inline SQL, prepared queries, or stored procedures. ADO.Net offers a few too. PHP must have a few choices as well but I've never looked closely at it, being more of an ASP guy and doing less Web development in recent years.

But it would be great to start somewhere, and I'm sure C# programmers can easily take VB.Net examples and run with them.

[dilettante]

Ahh, the classic cartoon: Exploits of a Mom (http://xkcd.com/327/).

[si_the_geek]

I've got the first part ("general theory") done, and added it to the FAQs: Why should I use Parameters instead of putting values into my SQL string?

It includes links to existing articles with Classic VB/VB.Net/C# examples, and later I'll add links for the code based articles.


Feel free to comment on it, I'm sure there are parts that could be improved.