Simple MySql or MySqli Class

Question

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?

• mickmackusa: Your question title is not meant to address your concern, it is meant to describe what your code does.
• BCdotWEB: FYI You should use parameterized queries: acunetix.com/blog/articles/…
• John Conde: The answer will be "they're both unsafe".
• Your Common Sense: The second one just adds no value, so it is useless. See how to connect with mysqli properly, then how to query with mysqli properly and finally how to reduce the insane amount of code when you need to run a prepared query only once (99% of time)
• Ingus: @JohnConde how can i fix that in simple mysqli example? And how can i test/hack it?

Answer 1

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>";
}

• MaKR: Thank you editor for fixing my idiot mistakes in my hasty response
• Your Common Sense: 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.
• Ingus: 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?
• Ingus: @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?
• MaKR: 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/…