How to protect my website against SQL injection?

Question

As a Web developer, after building an expensive PHP website for your client, some time later, hackers, by many attempts, try to hack the said-website because of its fame or for any other reasons.

Which security measures should you take in such situation?

Answer 1

Thanks Ipang.
I also think the POST method is preferable than the GET method and I've also seen that we can use PDO to prevent SQL injection.
What is actually PDO?

Answer 2

From database security I guess we can agree that we should never use an administrative account/user for production stage, and always setup appropriate privileges for access down to table level.

A firewall system to bridge the server with the net may help to reduce, not totally, the chance for intrusions.

Carefully work on any code related with data, use prepared statement may also help, but we all know, no such thing as perfect, so we can try but we can't expect zero incident.

Always update the system (Windows in particular), an unpatched security issue can become an exploit for one's ignorance. And of course update human resources too, being left behind on an issue and its solution can lead to an awful lesson.

Just some of the points I read on articles, I'm no security expert, I could even be wrong here and there, it's part of learning : )

Hth, cmiiw

Answer 3

PDO (PHP Data Objects) is another way to access and manipulate a database. It is one of the alternatives to mysqli and offers more support for other databases.

http://php.net/manual/en/intro.pdo.php

Answer 4

By itself that does not guarantee that your site is safe, but it does guarantee that the driver is up to date and has support (which provides security against deprecated alternatives)

Answer 5

Thanks so much @Mickel

Answer 6

Thanks @Derrick and @Mickel
So if I have, for instance, Microsoft SQL Server, then I need to install the PDO_DBLIB driver only and configure it and my website is safe?

Answer 7

Kindly use PDO. It has an advantage over mysqli, supports a variety of databases that a PDO driver has been written for, exampels:

PDO_DBLIB ( FreeTDS / Microsoft SQL Server / Sybase )
PDO_FIREBIRD ( Firebird/Interbase 6 )
PDO_IBM ( IBM DB2 )
PDO_INFORMIX ( IBM Informix Dynamic Server )
PDO_MYSQL ( MySQL 3.x/4.x/5.x )
PDO_OCI ( Oracle Call Interface )
PDO_ODBC ( ODBC v3 (IBM DB2, unixODBC and win32 ODBC) )
PDO_PGSQL ( PostgreSQL )
PDO_SQLITE ( SQLite 3 and SQLite 2 )
PDO_4D ( 4D )

Answer 8

these are some "best practices" i found scattered through SO, php manual, sql injection related sites, while making my CMS:

you can use pdo or mysqli for prepared statements depending on the db you're using

consider using a separate database for the users login credentials.

store only passwords hashed with a secure and random salt.

prevent the users accessing the db, from altering its structure (drop tables)

treat any client input as unsafe: trim, filter, validate before doing anything with it.
php has some filtering function for the purpose.
if yoy expect an alfanumeric input trim every char that is not a-zA-Z from a string.
if you expect a number trim everything that isn't 0-9,.+-

do not allow "--" in your input.
ore use [ ] so that if part of the query gets commented out , it will throw an error due to the missing ]

Set user session timeouts and validate user requests with a random token generated at login.

use ssl encryption (buy a certificate) otherwise all the above is a wasted effort.

there is more and the deeper you go in the sec'ty rabbit hole the worse the user experience will get.