I am trying to convert over to MySQLi and wanted expert advice. Is the enclosed code 100% OOP. Also, how secure is the code against attacks? I know nothing is 100% secure, but how good/safe is it? How and what can I make better? This is just a generic query as I wanted to get a basic model setup.

define ('DB_HOST', 'localhost');                       //  Host name
define ('DB_USER', 'root');                            //  Mysql username
define ('DB_PASSWORD', 'password');                    //  Mysql password
define ('DB_NAME', 'database');                        //  Database name

// New Connection
$mysqli = new mysqli(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME);

require("dbcon.php");  // This is actually the code above in a separate file that I have included here for checking.

$query = "SELECT id, username, firstname FROM users WHERE firstname=?";

if ($stmt = $mysqli->prepare($query))
   // Bind the variables
   $firstname = $_POST[first_name];

   // Bind parameters  s - String, b - Boolean, i - Integer etc
   $stmt->bind_param("s", $firstname);
   // Execute it
   // Store it for num rows
   $num_rows = $stmt->num_rows;
   // Assign blank variables
   $id = "";
   $username = "";
   $firstname = "";
   // Bind the result and retrieve the data
   $stmt->bind_result($id, $username, $firstname);
      if(($num_rows) > 0)
            echo $id;
            echo "<br />";
            echo $username;
            echo "<br />";
            echo $password;
            echo "<br />";
            echo "Number of rows: ", $num_rows;
            echo "<br />";
            echo "<br />";
         echo "No records";
   // Close the statement
// Close connection

2 Answers 2


Your binding of parameters will help in preventing SQL injections.

You need to sanitize your data first though. You need to treat everything that comes from a user (passed from the client machine) as 'dirty' and not allow it into your system before it's been 'cleaned'. Your first step would be to clean it - converting all HTML tags and special characters into their HTML entities htmlspecialchars() htmlentities() (well as long as HTML isn't allowed). This will help prevent cross-sight scripting (XSS). Typically then you would move on to validating the data - making sure it's in a format you expect. Then if it's not you return it to the user with an error. This is also assuming that you've checked the user has permission to run this function, if relevant.

  • \$\begingroup\$ ok, the validate and santize issue I was going to work on after I got this template started. Other than that, is it 100% oop, safe and secure enough to use? \$\endgroup\$ Feb 24, 2014 at 0:17
  • \$\begingroup\$ It's pretty good. Obviously it's hard to give a proper review as this is only a small snippet of example code - obviously normally you wouldn't be echoing out the password etc., would have the connection details in a separate file (as you commented you do) and would hash (and salt) the password before putting into the db. OOP-wise I would load the user input into the relevant object - in your example this would likely be a User class - before using it on the page (e.g. to return it to the user if validation failed). So it would be echo $user->getId() for example. \$\endgroup\$
    – user37349
    Feb 24, 2014 at 11:47

Yes, it looks like you're exclusively using OO style.

Your code will prevent SQL injection attacks by using MySQLi prepared statements as you do, but you're not doing anything to prevent a potential XSS attack through the output you're sending to the browser. To do this, escape characters that could be used in an XSS attack before sending them to the browser, for example

echo htmlentities($username);

It's unclear whether this is code you intend to use or if you're just illustrating concepts, but what you've posted exposes usernames to anybody who can access the script in a browser. You need do several things if you're actually going to run this code. The first that comes to mind is checking to see if the user is logged in under an account allowed to view usernames.

Also I see a variable named $password although it's not part of the select. It's unclear why you have this variable, but you should never store passwords in a database without salting and hashing them - which makes it useless to display in the browser as you seem to want to do here. Here is an article on the topic: https://crackstation.net/hashing-security.htm

  • \$\begingroup\$ Thank you for your comment, and yes, I will use both htmlentities and the mysqli real escape. In hindsight, I should have added it earlier for other to see and perhaps use. The $password is actually a line of code that I copied accidentally and does not belong there. At least I know I am on the right and safe track. I am not sure if I should mark this as "answered", but cheers to all. \$\endgroup\$ Feb 25, 2014 at 2:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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