The most common security mistake you can make is passing data around in forms that shouldn't be there.
There was an article on the reg the other day (www.theregister.co.uk) that said that you could solve the security issue presented by passing a SQL query in a GET method (i.e. postpended on an url like so: somepage.html?sql=insert+into+name+values('stan') (probably not a correct URL because we need to URLENCODE it, but you get the idea) by changing to a post method. (i.e. not visible as part of the URL).
But the problem here isn't HOW we're passing the query, it's that we're passing it period.
If what you want to do is insert the name, then the only thing the form should feed you is the name. Period. Not the column name, not the table name, not the database name.
the other common mistake is using an undefined variable to concatenate data on the end.
If we have a look like this:
for ($i=0;$i<$var;$i++){
$data.=$somefield[$i];
}
we need to have a line like this above it:
$data="";
or if (isset($data)) unset ($data);
to clear it.
In short, treat everything that comes from the user agent with suspicion, because even if they aren't trying to hack you, since TCP/IP and http do NOT have error checking in them to make sure the data that got transmitted was the correct data, you can't be sure of what you're getting.
Another common mistake is to put the data in a drop down, then refer to that.
I.e. let's say we do this:
select buildings from options:
result set:
"building a"
"building b"
"building c"
then we build a select box with it:
[select name=choice]
[option value="building a"]a[/option]
[option value="building b"]b[/option]
[option value="building c"]c[/option]
[/select]
Now, after receiving the data back from the form, we build a sql query from it like so:
insert into userdata (location) values ('$choice');
We are taking the data from the drop down, and inserting it right into the database.
This is just wrong on many levels, but many folks do it.
Better to do it this way:
select id, buildings from options;
1,"building a"
2,"building b"
3,"building c"
then our select box has options that look like this:
[option value=1]a[/option]
and so on.
Our insert is now changed to:
insert into userdata (location) values ($choice);
With a foreign key constraint on the userdata table's locate (now an integer, not a text field) pointing to the options table's id field, even if things go wrong, the worst that can happen is that the wrong building will get entered, not garbage or a non-existant building like "McDonald's Playland" or something.
So, in closing, always pass a REFERENCE to data you already have, not the data. It's much more likely to get corrupted if you're tossing the actual data around in forms when you don't need to.
