Preventing SQL Injection

[leesiulung]

I'm looking for ways to prevent SQL injection attack and happen to find an excellent article by Ben Forta:

http://br.sys-con.com/read/165921.htm

However, he does not mention how to prevent fields that contains strings, such as names. Doing an IsValid("string", fieldnames) does not prevent someone putting in SQL into a field when validating the data.

Bottom line is: How do one validate fields that contains strings to prevent someone from injecting sql?

[Newsgroup_User]

leesiulung wrote:
> I'm looking for ways to prevent SQL injection attack and happen to find an
> excellent article by Ben Forta:
>
> http://br.sys-con.com/read/165921.htm
>
> However, he does not mention how to prevent fields that contains strings, such
> as names. Doing an IsValid("string", fieldnames) does not prevent someone
> putting in SQL into a field when validating the data.
>
> Bottom line is: How do one validate fields that contains strings to prevent
> someone from injecting sql?
>

<CFqueryparam ...> This is one of the biggest reasons for using this
tag, to prevent SQL injection. By doing this all your query values are
turned into parameter variables. Thus any passed in strings are not put
directly into your SQL string, but passed seperatly as identified and
typed pieces of data. Thus if you tell your DBMS that a value is a
string, that database says this is a string and I will put all its
characters into the field.

Thus if anybody tries to pass a SQL injection string to your query, all
that happens is you save all the characters into your database, or as
many as the field allows.

Use <CFQueryParam...> always, it is your friend.

[coderWil]

I would use <cfqueryparam ...>

This tag sends a representation to the database that the variable inside it is a variable value. If an injection is attempted, the 'additional code' added to the query will be treated as a variable rather than sql code.

eg.

select * from customers
where username = <cfqueryparam value="#form.username#">
and password = <cfqueryparam value="#form.password#">

This would stop a person who sent a password value of say:
wrong' or 1 = 1 or username <> '

So instead of messing with the sql, the query is looking for a password that exactly matches the above line of text.

Please note 2 things.
1. When using cfqueryparam, do not surround it with quotes to designate a char value.
2. You will use all ability to cache a query by using this tag

Hope this helps

[drforbin1970]

CFQUERYPARAM is indeed a must. Don't forget to use CFTRY/CFCATCH everywhere. Besides SQL attacks, if you don't validate your data used by CF, your code could 'break', thus dumping valuable info in an error message. Make sure you validate all user input and use CFTRY/CFCATCH.

Test inputted data for what is allowed, instead of for what is NOT allowed.

[Freffie]

Hi. Does anyone have an experience using CFQUERYPARAM when your query is stored in a string variable?

[leesiulung]

drforbin1970, can you give me an example of how to use this cftry/catch with the cfquery?

Thanks,

[drforbin1970]

Check documentation for CFTRY/CFCATCH. More info there than I can give you.

[Rushglen2]

SQL injection comes in many forms, here's a good read about myspace.com

but most of all, samy is my hero

and how he did it (the code):
http://namb.la/popular/tech.html

Andrew