Important: Secure your SQL queries!!!

[SpazBoy]

Thanks for the link bbolte, I read that one already (I think it was a link in previous posts). That article mainly covers ASP. Of course, its still good to know if I need to use ASP in other projects. But as of now, I am looking for PHP resources. I have found some like the articles and posts that Dr. Pepper and HarryF has made.

ex. magic quotes are a NO NO
so I use the addslashes()
and the ereg_replace( ) to replace any suspicious characters.

I hope I am using the right tools..


Spaz

[Antonis]

I'll feel really stupid if anyone has already answered this and I somehow didn't realize it, but are there any handy PHP equivilents to the ASP functions all those aritcles seem to be using? Such as one that replaces the ' with ''. Also is there an easy way to see if there are certain characters in the userinput that we might want to keep out? I'm new to this--please, don't yell at me.

[MarcusJT]

Here's another ASP-orientated one, although as ever, the same principles apply to all development platforms...

http://www.4guysfromrolla.com/webtech/112702-1.shtml

[MarcusJT]

And another, specifically on using MSSQL stored procedures to avoid injection attacks:
http://aspalliance.com/385

[marycp]

Cold Fusion has protection against this by using <CFQUERYPARAM...> to ensure that the data meets the requirements. You can <CFTRY> and catch the error. It can mean the end of your data if someone sents in a delete command as an ID.

[Subjective Effec]

I'm using php/mySQL.

I have a site that has no accounts, and no way for a user to (legally) write to the database, which only lets the user query a database

Simple Q: Can php/mysql write priveledges be set so that there is no way for injections to alter the database? (ie the only way to alter the db is to log into the host account and use phpAdmin).

[MarcusJT]

Hot off the presses (so hot, it's dated september, but it's only August the 20th):

MSDN: Stop SQL Injection Attacks Before They Stop You

[hgilbert]

go stored procedure

that is one thing that keeps me off mySQL and PHP to a certain extent
you have to be very aware of security issues here
when mySQL present something akin to stored procedures I will jump in

[Aleksejs]

How to write injection-proof PL/SQL
67 pages on the matter

[Dr_Ante]

I thought I'd better make sure that everyone is aware of a particular class of security vulnerabilities called malicious SQL code injections. While there are many other security vulnerabilities, these Here's a few sites to start you off (!):
M@rco

If you use parameters in your query, than there is no chance to make SQL Injection. You can also use LINQ.

[Aleksejs]

One of this years pwnie awards nominies:
50 ways to inject your sql

[r937]

50 ways to inject your sql

10 seconds of lame singing to find the back button

cute idea, though

[tutistech]

Setup and execute all queries as stored procedures. The way SQL parameters are passed prevents the use of apostrophes and hyphens in a way that would allow an injection attack to occur. In addition, it allows database permissions to be restricted to only allow specific procedures to be executed. All user input must then fit into the context of the procedure being called and it is less likely an injection attack could occur.

[Amit Yaron]

If possible, use HereDoc or NowDoc, which escapes input strings.
When the input string is escaped a query won't look like
SELECT *
FROM users
WHERE username='admin'; -- '
AND password = ....

(username='admin' and the rest is a cooment.)

NowDoc, will make it:
SELECT *
FROM users
WHERE username='admin\';--' ...

[r937]

If possible, use HereDoc or NowDoc

you might want to explain which language(s) these are available in

[Amit Yaron]

you might want to explain which language(s) these are available in

You are right. I know of two languages that support HereDoc and NowDoc:
PHP and Ruby.

In PHP, you would use:
$myString=<<<EOS
.
.
.
EOS;

OR <<<'EOS';

In Ruby, you would use '<<' instead of '<<<'.

[badmood]

go stored procedure

that is one thing that keeps me off mySQL and PHP to a certain extent
you have to be very aware of security issues here
when mySQL present something akin to stored procedures I will jump in

[r937]

when mySQL present something akin to stored procedures I will jump in

news flash... mysql version 5 supports stored procedures

mysql version 5 came out in beta in march 2005 and in production release in october 2005

the time is well overdue for you to jump in

[Darkwind]

Great discussion.

How much overhead will it create if a coder were to create an abstract layer that performed security checks on any data that was to be parsed to the database? What I mean is, if every form, or user input were to be passed to a security object that would then scrutinize the data before returning it to the database update routine, would it add significant overhead to the performance of the script?