Web Security? Sql Injections?

[skaterdav85]

I recently created a coldfusion site using an access database. I did both client (javascript) and server side validation so that all form fields wont accept characters like * < or > or certain words like (insert, delete, update). I also put maximum lengths on all form fields through server side validation. I was wondering what other measures i could do that would prevent hacking or sql injections. Im not too familiar with sql injections and how they work, so any feedback would be appreciated!

[Synook]

You need to escape special characters, by placing backslashes before them so that they aren't parsed by the SQL interpreter. Special characters include things like quotes, wildcard characters (* and _ for Access), query delimiters ;, and comments. A simple SQL injection example - take this query.

SELECT * FROM table WHERE password == '$pasword'

If quotes were not escaped a hacker could enter "' OR ''='" for $password, leading to

SELECT * FROM table WHERE password = '' OR ''=''

[skaterdav85]

You need to escape special characters, by placing backslashes before them so that they aren't parsed by the SQL interpreter. Special characters include things like quotes, wildcard characters (* and _ for Access), query delimiters ;, and comments. A simple SQL injection example - take this query.

SELECT * FROM table WHERE password == '$pasword'

If quotes were not escaped a hacker could enter "' OR ''='" for $password, leading to

SELECT * FROM table WHERE password = '' OR ''=''

Basically what i did was on my process page, if any of the form fields contained * or < or >, the page would give an error message and not load the rest of the page or process any of my server code. does that work partially to?for a sql injection to work, wouldnt the hacker need to know the db table and field names? the file permissions were changed on my db file so that someone browsing couldnt view it.

[stifu]

Basically what i did was on my process page, if any of the form fields contained * or < or >, the page would give an error message and not load the rest of the page or process any of my server code. does that work partially to?

That's not enough. Watch simple and double quotes too. There may be existing function just for that in the language (which is it?) you're using.By the way, hyphens can be used to trigger an SQL comment and ignore the rest of your query, too (however, those could be used in a valid password, unless you restricted what users can use).

for a sql injection to work, wouldnt the hacker need to know the db table and field names? the file permissions were changed on my db file so that someone browsing couldnt view it.

Not necessarily. You could do an SQL injection by adding stuff like "OR 1=1", which would always work no matter what.You may find this interesting: http://www.unixwiz.net/techtips/sql-injection.html (just found this through a quick Google search)

[skaterdav85]

That's not enough. Watch simple and double quotes too. There may be existing function just for that in the language (which is it?) you're using.By the way, hyphens can be used to trigger an SQL comment and ignore the rest of your query, too (however, those could be used in a valid password, unless you restricted what users can use).Not necessarily. You could do an SQL injection by adding stuff like "OR 1=1", which would always work no matter what.You may find this interesting: http://www.unixwiz.net/techtips/sql-injection.html (just found this through a quick Google search)

I created the site in ColdFusion. Are you familiar with the language? Right now the page errors if any of the following words or characters are submitted.* < > = - _ ; insert update delete Would this prevent a good number of sql injections? I didnt want to eliminate single quotes, since that can be used regularly and i didnt know how to parse that out.

[Synook]

... when thinking about SQL injections, you need to remember that what you are running on the database server is SQL code, not CF code! So, while the lt and gt symbols have have special significance in ColdFusion they have no such meaning in SQL.And you don't need to deny quotes from being entered, you just need to escape them using the aforementioned backslash character. " -> \" : problem solved. And you NEED to negate quotes, as they are the overwhelmingly most dangerous character because they can be used to exit from strings.In PHP, the mysql_real_escape_string() function escapes the following characters: \x00, \n, \r, \, ', " and \x1a.

[justsomeguy]

Would this prevent a good number of sql injections?

Is that all you're after? Preventing "a good number"? Here's the thing: this is sort of like nuclear defense (stay with me). If you prevent "a good number" of nuclear attacks, that doesn't really matter if they got one through. The attackers only need one success, but you need a 100% success rate. This isn't about stopping most attacks, it's about stopping all attacks. If you only want to stop most attacks, it's probably fine if you don't allow words like "update" or "delete", but don't be surprised if someone attacks you without using those words.

I didnt want to eliminate single quotes, since that can be used regularly and i didnt know how to parse that out.

It's not about eliminating anything, it's about santizing the data. Instead of not allowing characters (the database applications I write allow everything into the database - if you want spaces in your username or password, or want to use hex characters or whatever, fine), just sanitize the data to make sure it's not going to break anything. First, this means escaping quotes. You need to turn ' into \'. Also, be aware of what data type you're working with. Just escaping quotes will work for string values that are themselves wrapped in quotes:SELECT * FROM table WHERE field='value'In that case, just escape quotes on the value. If you're working with data that is not quoted, like a number:SELECT * FROM table WHERE id=10Then for those you need to convert the values that you get submitted to a number. Escaping quotes doesn't help if it's a number value that you're inserting into the query that isn't going to be quoted anyway.

[skaterdav85]

... when thinking about SQL injections, you need to remember that what you are running on the database server is SQL code, not CF code!

So would i be using a function in SQL that escapes out quotation marks?

Is that all you're after? Preventing "a good number"? Here's the thing: this is sort of like nuclear defense (stay with me). If you prevent "a good number" of nuclear attacks, that doesn't really matter if they got one through. The attackers only need one success, but you need a 100% success rate. This isn't about stopping most attacks, it's about stopping all attacks. If you only want to stop most attacks, it's probably fine if you don't allow words like "update" or "delete", but don't be surprised if someone attacks you without using those words.

haha good point.

[justsomeguy]

PHP has a function that escapes quotes and a few other characters, and another function to convert to an integer or float. I'm sure ColdFusion has something similar. All you need to do to escape the quotes is to replace a single quote with either 2 single quotes (not a double quote) or a slash followed by a single quote.

[boen_robot]

I searched the Adobe Developer center, and found this article, which has some techniques for escaping and checking input to the DB.Additionally, you may want to review the Cold Fusion documentation on SQL communication.BTW, what Synook meant to say is that Cold Fusion creates one SQL statement - nothing more and nothing less. It's up to you to form it. Once you do, it's this SQL statement that the DB server runs. It sees none of your CF code, so it can't determine what you really had in mind. It just blindly follows it. If you had an integer in the SQL statement, you'll have an integer, etc. - it up to CF to make sure that what you have there is indeed an integer. If you have a string, it's up to CF to make sure that this is indeed one plain string in SQL, and not a string along with an additional SQL command. "x' OR '1'='1" is a string from CF's perspective, but if its assembled as is in "SELECT password FROM users WHERE password = 'x' OR '1'='1'", that's still a valid statement that will be executed. If it's escaped, so that it becomes "SELECT password FROM users WHERE password = 'x\' OR \'1\'=\'1'", that's still a valid statement that will be executed... only this time it will mean a different thing - the thing you meant it to mean.