storing query failures and access via admin ctrl panel

[dmmh]

I am pretty concerned with security so Ive been thinking alot about how I tackle various problems.

Basically I want to be able to log failed/ suspicious queries which contain stuff like: x'; DROP TABLE tablename; -- etc etc into a table...however everywhere I see people use textfiles. I wonder why this is?

Id like to be able to access suspicious queries via a part of my admin control panel, view the details and ban the user's account, his Paypal adress and account associated email adres with a press of the button if it contains malicious entries.

It isnt very hard to do, I just wonder why people choose text files...which are easier to access if need be then a table (assuming all scripts are pretty secure ofcourse)

[dcrombie]

The problem with logging SQL injection attempts in a database is that one of them ever worked the intruder could potentially DELETE everything you'd logged. It's much more secure to use a text-file that lies outside the web directory.

[dmmh]

im enough arrogant to assume that will never happen.... :D

[jollymcfats]

People often log to a text file because it's simpler and thought to be faster. But file-logging exceptional events with file locking isn't necessarily significantly faster than logging to an optimized table, if you already have a connection open.

With the extra power of table storage comes extra complexity- you can't just make a simple file copy of your log for analysis offline, trimming and rotating it requires code instead of just a file delete, cron jobs may be involved, etc.

[jatar_k]

the other option is syslog to log with but there is a limit of 256 chars per entry/line so I switch anything that may be longer than that, such as query strings, to a standard textfile.

[dmmh]

thanks for all the replies people

The problem with logging SQL injection attempts in a database is that one of them ever worked the intruder could potentially DELETE everything you'd logged. It's much more secure to use a text-file that lies outside the web directory.

I can understand that, but why then is there something like session_set_save_handler()?
Doesnt this impose the same problem?

I mean, if one is to assume the server will be hacked sometime, my would you wanne use this?

Ofcourse, you could argue to encrypt the session data first, but the same could be used for the database log :)

[dmmh]

any takers? :)

[mincklerstraat]

I think the responses above intended something like just a plain text file, and not saving as some kind of session (which would disapper anyways unless stored in some non-session format). This wouldn't be affected by session_set_save_handler(). Must also agree - either you're interested in your database security, or you're not (maybe because you're convinced your script and server are pretty much bulletproof). If there is any chance of database compromise, text logging is the way to go, since your database may be compromised / non-existent, or you may be offering new holes for secondary exploits - e.g., the query, when caught, gets 'cleaned' and put into the database - but the exploit is actually one which uses your cleaning process to create what it wants to inject. Textfiles are realively simple things, the security issues are less complicated. And if you're bulletproof, why care what the scriptkiddies are throwing at you?

[dmmh]

That I understood, I was just wondering why people would use session_set_save_handler() which stores session data in a table if you are gonna asume the server will be compromised at some time, especially because people tend to do it for extra security? Isnt this like contradicting?

Its a different example, but I use it because it applies to what I wanne do. And I really dont think my site/ server is bulletproof :)

[mincklerstraat]

When you think security, think layers. If you have a very well-coded script, your server is in a secure place, and it isn't shared, chances are you'll never need your fall-backs. But if your database is compromised, you'll want to have something to help diagnose the problem that's independent of the database. This isn't just the same as assuming your database is open prey to all types of attacks.

When your script gets bigger and more complicated, thinking in layers helps too. If you're 100% sure that all user input is being checked that it, for example, only contains alphanumeric characters, and it otherwise stops execution, you won't have to check it again or escape it when you make a query out of it. Or if you have only one search script, and this allows for non-alphanumeric data, and you are positive it can't be accessed by any other script, you know you only have to check this one script and add slashes for this script's queries. However, if you have a pretty complex script where data is being handed back and forth, you may just decide to add slashes to all info that gets used for making queries, even if it is "supposedly" from a source that has to be an integer, and you're fairly sure that this is so. Why? Just in case somewhere there's an eventuality you hadn't thought of, or something you failed to check. You're pretty confident that this isn't the case. But if it barely costs you anything, just go ahead and do it. Same for displaying data - if you have a fail-safe set of classes that rigorously cleans everything before output, you're pretty safe. But if this isn't the case, and you have data that you are very sure of, but the degree of complexity means you're less than 101%, go ahead and use htmlspecialchars on it just to be sure - it doesn't cost you so much in terms of script execution time.

This is sort of like having backups, and backups of your backups, and off-site backups. Nobody's assuming your house is going to burn down. But if it does, thank God for backups.