Best practice says to do everything you can in the name of security.
But the practical side of me says don’t bother. The biggest risk SQL injection poses on the web is exposing private user information, which restricting write access doesn’t help. Backups are your protection against lost or corrupt data, and you need them anyway because SQL injection isn’t the only way that can happen.
Many people that run websites with databases on shared hosting don’t even have the option of creating their own users with specific permissions. The users they connect as are made through a control panel that gives them full access to that user’s database.
I looked into this and I DO have the option to set read-only permissions to my mysql users.
Fair enough. It’s not a much extra work, but I see your point. I don’t see how my database is really much safer when SELECT based attacks are really just as dangerous.
As for backups, I already have a SSH mysqldump cron job set up on my server. It automatically performs daily backups of my databases and stores them as SQL files in a protected folder that only I can access. It’s a higher level folder, and is not located within my public HTML folder (since I hear .htaccess passwords can be bruteforced)… so I hope these are safe! I download these files every two weeks and keep local backups.
The scary thing is, I have already designed a few database driven websites for clients… but I never knew about SQL injection until recently! yikes!
PS. On the subject of validating variable types, such as integers… would filter_var() be a good method? Or is better way?
Out of curiosity, what were you doing about quotes before? Weren’t your sites breaking as soon as someone typed a single quote into an input destined for the database?
…sorry I just realized you already showed me a way using:
$number = (int)$_POST['something'];
So I guess it just depends on whether I want to check the variable, or cast it as an integer.
Also, what’s the difference between bindParam and bindValue? Right now I am using bindParam. Is one safer than the other?
Thank you again for your help everyone! Lots of great information. I think I have a good start on building secure sites with MySQL and PHP now. First I have to secure up my past clients’ sites.
filter_var is a very good way to filter different types of data from variables.
Although, I have been known to use more than my fair share of intval(). Even though it does the same job as casting to (int) I somehow feel better with intval().
Either way though, regardless of whether filter_var(), (int), or intval() are being used, it makes sense to sanitised and validated all inputs before they are used. Otherwise you are leaving yourself open to a wide range issues that weren’t otherwise caught.
I did check the manual, however I asked because I do not understand what they mean by “reference” VS. “value”. From what I gather, bindParam evalutes the variable at the time when execute() is used. Whereas bindValue evalutes the value immediately, so if the variable data is inserted into the statement before execute() is used.
