Creating a secure and efficient DB connection and queries (using one class) Currently, I'm working with a PHP database connection class that is in one single file with a construct. Every time I need this connection, I instantiate the class and the construct creates the connection with only having to modify the user, password, DBname and host.

It works fine and I haven't faced any problems with this class. Although I want to start taking security seriously in my web applications, I've been looking for a way to implement security (SQL Inj, XSS, DoS, etc.) and use updated functions, prepared statements, escaping, etc. My problem is that I haven't found a single answer about this exactly because I don't call the mysqli_connect function in index.php. I just create the object for the Connection class and use it.

&lt;?php
    class Connection {
     private $connection;

     private $host='localhost';
     private $user='user';
     private $pass='pass';
     private $database='dbname';
     private $n=0;

    function __construct() {
        $this-&gt;connection = mysqli_connect($this-&gt;host, $this-&gt;user, $this-&gt;pass, $this-&gt;database);

        mysqli_query($this-&gt;connection,"SET NAMES 'utf8'");
    }

    function sql($sql) {
        if (is_array($sql)) {
            foreach ($sql as $s) {
                $res = mysql_query($s);
                if (mysql_errno()) echo mysql_error().'&lt;br&gt;';
                $this-&gt;n++;
            }
        } else {
            $res = mysqli_query($this-&gt;connection,$sql);
            if (mysql_errno()) echo mysql_error().'&lt;br&gt;';
            $this-&gt;n++;
        }
        return $res;
    }

    function __destruct() {
        if (is_resource($this-&gt;connection) )
        mysql_close($this-&gt;connection);
    }

    function getLink() {
        return $this-&gt;connection;
    }
}


So if I want to Create, Read, Update, Delete Something I do this in another file: User.php (Which i want to use the new method I'm asking for and use prepared statements for the Queries here and escape functions)

&lt;?php
  // Declare a variable
     private $user;

  // Setter
     function setUser($val){
        $this-&gt;user = $val;
     }

  // Create Function to CreateUser
     function CreateUser(){
        $con = new Connection();
        $sql = "INSERT INTO user (DATA) VALUES(DATA)";
        $con-&gt;sql($sql);
     }

  // Same for all CRUD i just change the Query of course.

?&gt;

&lt;?php 
  // At the index.php i do this:
  include_once 'User.php';

  $user = new User();

  if(isset($_POST['createUserButton'])){
     // $The variable i set in my User Class
     $user = $_POST['user'];

     // Then the Setter
     $user-&gt;setUser($user);

     // Then execute the Function
     $user-&gt;CreateUser();
  }  
?&gt;


I'm trying to translate this class using:


New versions of functions
Using try/catch
Security


I'm just trying to have as many points of view from more experienced developers. I'm a few months old at this and I'm trying to look at different methods to code so I can create my own.
You say that you use prepared statements, but your code doesn't actually reflect that. 

sql just executes any query, without binding or escaping anything. You really need to use prepared statements though. Your current sql function isn't usable in practice and it's pure existence will likely lead to vulnerabilities (other programmers will likely use it as it exists, and it can't be used securely).

Apart from that:


don't mix different APIs. Currently, you use mysqli and mysql.
don't use mysql, it's been deprecated for years and has finally been removed in PHP7.
your formatting is off. Use correct indentation, always use curly brackets, use newlines for statements (eg after an if), and use spaces around =. Any IDE can fix these things for you.
be consistent with your naming. Your functions should all start with a lower-case character.
Also, same things should be named the same. So if you have a field called connection, the method retrieving it should be getConnection, not getLink.
Also try to be more explicit with your variable names and don't shorten names. val should be value (but really, it should be user or username), res should be result, n should be queryCount, and so on.
It's very unclear to me what n is used for. I would remove it, or document it.
sql isn't really useful. It's just a wrapper around mysql_query which automatically echoes errors (meaning that the calling code has no possibility to recover or show a different error message; throw an exception instead), and provides the possibility to execute multiple queries, which doesn't actually seem needed.
You don't want to create a new database connection for each method (for performance reasons), so instead, create it once and then pass the db object to methods that need it.