1
\$\begingroup\$

Which one of these methods is better/safer to use? And what benefits could I get using one or other?

Simple mysqli:

connection.php

$DBServer = "localhost";
$DBPort = "3306";
$DBUser = "root";
$DBPass = "";
$DBName = "test";

$conn = new mysqli($DBServer, $DBUser, $DBPass, $DBName, $DBPort);
if ($conn->connect_error) {
    echo "Database connection failed: " . $conn->connect_error, E_USER_ERROR;
}
mysqli_set_charset($conn,"utf8");

index.php

include_once("connection.php");

$l_name = mysqli_real_escape_string($conn, $_GET['l_name']);

$query = mysqli_query($conn, "SELECT * FROM test WHERE lname='".$l_name."'");
while($row = mysqli_fetch_array($query)){
    echo $row['f_name'].' '.$row['l_name'].'<br>';
}

mysqli with class:

connection.php

class Connect
{

    var $host = 'localhost';
    var $user = 'root';
    var $pass = '';
    var $db = 'test';
    var $con;

    function connect() {
        $con = mysqli_connect($this->host, $this->user, $this->pass, $this->db);

        if (!$con) {
            //die('Could not connect to database!');
        } else {
            $this->con = $con; //echo 'Connection established!';
        }
        mysqli_set_charset($this->con,"utf8");

        return $this->con;

    }

    function close() {
        mysqli_close($con);
    }

}

index.php

include_once("connection.php");

$con = new Connect();
$con->connect();

$l_name = mysqli_real_escape_string($con->con, $_GET['l_name']);

$query = mysqli_query($con->con, "SELECT * FROM test WHERE lname='".$l_name."'");
while($row = mysqli_fetch_array($query)){
    echo $row['f_name'].' '.$row['l_name'].'<br>';
}

Can you review my code for security and best coding practices?

\$\endgroup\$
3
\$\begingroup\$

Don't use either - PDO is much more user-friendly and feature-rich. Generally a class will be nicer to write code around, and now that we have PDO we don't need to write that class. The following example is largely stolen from here: https://phpdelusions.net/pdo

$DBUser = "root";
$DBPass = "";
$DBName = "test";
$DBCharset = 'utf8mb4';

$dsn = "mysql:host=$DBServer;dbname=$DBName;charset=$DBCharset";
$DBOptions = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
try {
     $pdo = new PDO($dsn, $DBUser, $DBPass, $DBOptions);
} catch (\PDOException $e) {
     // to avoid a potential credentials leak through a stack trace
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

$stmt = $pdo->prepare("SELECT * FROM test WHERE lname = ?");
$stmt->execute([$_GET['l_name']]);
$data = $stmt->fetchAll();

foreach ($data as $row) {
    echo "{$row['f_name']} {$row['l_name']}<br>";
}
\$\endgroup\$
  • \$\begingroup\$ Thank you editor for fixing my idiot mistakes in my hasty response \$\endgroup\$ – MaKR Apr 18 at 19:28
  • 1
    \$\begingroup\$ I took a liberty to fix some code mistakes and made your reasoning factually correct. JFYI, mysqli is never going to be deprecated, it's perfectly all right. \$\endgroup\$ – Your Common Sense Apr 18 at 19:28
  • \$\begingroup\$ First a big thanks for your answer! However what would you suggest me to fix if i would want to keep simple mysqli example style? \$\endgroup\$ – Ingus Apr 23 at 5:33
  • \$\begingroup\$ @MaKR First a big thanks for your answer! However what would you suggest me to fix if i would want to keep simple mysqli example style? \$\endgroup\$ – Ingus Apr 23 at 5:33
  • \$\begingroup\$ The biggest issue with either solution is not using prepared statements. Here is a great mysqli explanation, and early in the article the author links to a class example he has written. At the end he also links to articles on pros and cons of both. I would suggest using a class. websitebeaver.com/… \$\endgroup\$ – MaKR Apr 23 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.