SQL Injection Help...

[eluser]HSKrustofsky[/eluser]
I am in the process of adding a comments portion in a few sections of my site. I am kind of using the process being used in the "20 Minute Blog" tutorial. One thing I am afraid of is possible SQL injection. Just wondering where/how to start in preventing this?

Thanks in advaced.

[eluser]Eric Barnes[/eluser]
Quick advice: 1 = Use the active record. 2 = Pretend everything is tainted coming from your users.

[eluser]BrianL[/eluser]
The following is a deliberate overload in information. I hope it is helpful . It is obviously overkill if you are making a simple blog attracting little attention, but if not read on:

The basics:

1. First learn about character encoding. Then understand that any SQL injection mitigation strategy based on searching for patterns is likely to fail because there are dozens of ways to represent a character that you will probably miss. Including advanced tricks like double encoding, or even simple ones known for years like null byte attack.

2. Next understand that mysqli_real_escape_string is not enough. This chapter although old is the fundamentals.

3. Then understand steps to process data. Authentication, authorization, validation, sanitization, escaping, and encoding are all separate steps. Most people only escape, then wonder why their applications are vulnerable to custom payloads. Type validation is particularly important and a cast to (int) for limit clauses for example can prevent annoying attacks not particularly dangerous but which will sap system resources. Understand in what order to do each step. For example, many people use htmlspecialchars or htmlentities before storage into a database. This is wrong. Data must be processed *after* retrieval from a database as well and encoded for the browser, not simply blindly trusted from the database.

4. Learn how to write properly formed SQL queries. The first link in step 2 is useful. Then learn how to use prepared statements with mysqli extension. They are a real pita since you can only bring one result at a time and there is no fetch_assoc or fetch_array to convert results easily into an array. But it is worth it, since it will form the foundation of your understanding of active record and transactions.

5. Finally, translate all this theoretical knowledge into practical steps. First, by downloading OWASP for PHP to solve character encoding issues. This series of tutorials is helpful. You must canonicalize before validation and before sanitization. Second, create your own validation and sanitization libraries (what will happen if a user sends say an array instead of an int does your application fail?) Third, decide how you are going to use SQL on a daily basis. This finally translates into using active record. But even active record will be vulnerable if you write insecure code. Create a list of best practices such as storing all cleaned input in the $clean array so you never accidently use unclean input.

6. Even after all this you may consider additional measures depending on how mission critical your application is. For example using OWASP's mod_security ruleset to rip out SQL injection before it even reaches the application. Also, follow the rule of least privilege so that even if SQL injection is successful, the database user does not have DROP or DELETE or CREATE privileges. SQL injection is also only one of the many vulnerabilities to be aware of but not to worry the top vulnerabilities like XSS, CSRF, and so on have been around for years and there are many tutorials on the net about securing your application.

Good luck

[eluser]HSKrustofsky[/eluser]
Thanks for the replies. Read the active record, and that's pretty much how I have been writing my code. I was checking around the forum, and supposidly CI has built in SQL injection protection. Is that true? Also, I read somewhere on the forums, and checked out the user guide on XSS filtering. How's that work out. I saw that it was easy to enable. Just a quick change in the config.php file.

[eluser]BrianL[/eluser]
Your question seems to be can you avoid learning about certain things and rely on CI to deal with SQL injection and XSS? Nobody can really answer that question for you; I've given you the resources now you can decide how much time you want to devote to this stuff .

With 32 posts seems like you want to become professional CI developer. If that is the goal I suggest understanding the underlying fundamentals, rather than relying just on active record and a config option. It may take many weeks to months but it's quite rewarding.

Meanwhile while you're starting and making a simple blog active record and turning xss_clean on all the time is more than enough.

[eluser]Buso[/eluser]
[quote author="BrianL" date="1280194973"]The following is a deliberate overload in information. I hope it is helpful . It is obviously overkill if you are making a simple blog attracting little attention, but if not read on:

The basics:

1. First learn about character encoding. Then understand that any SQL injection mitigation strategy based on searching for patterns is likely to fail because there are dozens of ways to represent a character that you will probably miss. Including advanced tricks like double encoding, or even simple ones known for years like null byte attack.

2. Next understand that mysqli_real_escape_string is not enough. This chapter although old is the fundamentals.

3. Then understand steps to process data. Authentication, authorization, validation, sanitization, escaping, and encoding are all separate steps. Most people only escape, then wonder why their applications are vulnerable to custom payloads. Type validation is particularly important and a cast to (int) for limit clauses for example can prevent annoying attacks not particularly dangerous but which will sap system resources. Understand in what order to do each step. For example, many people use htmlspecialchars or htmlentities before storage into a database. This is wrong. Data must be processed *after* retrieval from a database as well and encoded for the browser, not simply blindly trusted from the database.

4. Learn how to write properly formed SQL queries. The first link in step 2 is useful. Then learn how to use prepared statements with mysqli extension. They are a real pita since you can only bring one result at a time and there is no fetch_assoc or fetch_array to convert results easily into an array. But it is worth it, since it will form the foundation of your understanding of active record and transactions.

5. Finally, translate all this theoretical knowledge into practical steps. First, by downloading OWASP for PHP to solve character encoding issues. This series of tutorials is helpful. You must canonicalize before validation and before sanitization. Second, create your own validation and sanitization libraries (what will happen if a user sends say an array instead of an int does your application fail?) Third, decide how you are going to use SQL on a daily basis. This finally translates into using active record. But even active record will be vulnerable if you write insecure code. Create a list of best practices such as storing all cleaned input in the $clean array so you never accidently use unclean input.

6. Even after all this you may consider additional measures depending on how mission critical your application is. For example using OWASP's mod_security ruleset to rip out SQL injection before it even reaches the application. Also, follow the rule of least privilege so that even if SQL injection is successful, the database user does not have DROP or DELETE or CREATE privileges. SQL injection is also only one of the many vulnerabilities to be aware of but not to worry the top vulnerabilities like XSS, CSRF, and so on have been around for years and there are many tutorials on the net about securing your application.

Good luck [/quote]
great post

[eluser]HSKrustofsky[/eluser]
Not saying that I am not going to read it, or that I am ignoring it. I was just wondering since the site is new, and is not generating a lot of traffic yet(hopefully it will), the XSS filtering and the built in feature could be a temporary solution, until I get everything situated and initiate proper protection?

[eluser]BrianL[/eluser]
[quote author="HSKrustofsky" date="1280218932"]Not saying that I am not going to read it, or that I am ignoring it. I was just wondering since the site is new, and is not generating a lot of traffic yet(hopefully it will), the XSS filtering and the built in feature could be a temporary solution, until I get everything situated and initiate proper protection?[/quote]

Again nobody can answer that question truthfully because we know nothing about the purpose or reason for your site. Since you say it is a blog tutorial the answer should be yes. But security is not about proper protections but a time and convenience tradeoff for safety. The only person who can answer such questions is yourself . For such people like designers and maybe a person with 1 homepage or 1 blog learning security in depth is a waste of time since if it ever gets hacked they can restore from backups in minutes. More security always means less functionality in one way or another so there is never any "proper" level and must be decided by the purpose. At the minimum I would become familiar with OWASP top 10 exploits of which XSS and SQL injection are only 2, and even if I didn't know how to prevent them I would be aware of them.