This question already has an answer here:
- How can I prevent SQL injection in PHP? 28 answers
I'm developing in PHP since a couple of year by now and I am still wondering which is the best technique to interact with a MySQL database, to avoid SQL Injection and have good performance. I'm not including function or classes removed in PHP 7 since I'm using that version.
First of all, which is the safer way to create a connection to a database?
mysqli_connectfunction (its alias) :
$host = ''; $username = ''; $password = ''; $database = ''; $conn = new mysqli($host, $username, $password, $database); $conn = mysqli_connect($host, $username, $password, $database);
$dsn = 'mysql:host=localhost;dbname=testdb'; $username = 'username'; $password = 'password'; $options = array( PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', ); $dbh = new PDO($dsn, $username, $password, $options);
Querying a database
Then, which is the safer and the quickest way to query a database? This time I've got three ways, but I'm pretty sure that the first one isn't great due to SQL Injection.
mysqli_queryfunction (its alias) :
$sql = "SOME SQL"; $conn->query($sql); mysqli_query($conn,$sql);
$sth = $dbh->prepare('SELECT * FROM table WHERE foo=?'); $sth->bindParam('s', $foo); $foo = 'foo'; $sth->execute();
$foo = 1; $sth = $dbh->prepare('SELECT * FROM table WHERE foo = :foo_id'); $sth->execute(['foo_id' => $foo]);