I am on my way re-coding my old code using mysql function which is already deprecated.
I am using a class to connect on my database:

<?php
    
final class db{
     static $db, $pager, $pager_vars;

static function config($conf=false){

	self::$db = mysqli_connect($conf['host'], $conf['username'], $conf['password']);
    if (!self::$db) {
        die("Database connection failed: " . mysqli_connect_error());
    }

    mysqli_select_db(self::$db, $conf['database']);

  }
}?>

//This is how I use a query


<?php
     $sql = "INSERT INTO transactions_in(
				        transact_in_date,
					transact_in_note,
					transact_in_by_user,
					transact_in_date_added
					)
					VALUES(
					'{$transact_date}',
					'{$post['transact_note']}',
					'{$_SESSION['user_name']}',
					 NOW()
					)";

  db::query($sql);
  mysqli_close(); //close the connection

}

?>
  1. Am I doing the right way? I used mysqli_close(); to disconnect. But I have an error: "mysqli_close() expects exactly 1 parameter, 0 given..."
    What I have tried is to add the connection to my code e.g (mysqli_close(db::config($db))); but I got the same error.

  2. And is there a way I can prevent an SQL injection on my code? Like using mysqli_real_escape_string? How to do it on my code structure?

xkevin Am I doing the right way? I used mysqli_close(); to disconnect. But I have an error: "mysqli_close() expects exactly 1 parameter, 0 given..."
What I have tried is to add the connection to my code e.g (mysqli_close(db::config($db))); but I got the same error.

No. One of the points of having a database class is so that all the interaction with the database is through that class. You should have a close() method in the class, assuming you even want to close the connection yourself (php will automatically close any database connection when the script ends.) If you did have a need to reference the connection in your main code, it would be in -

db:$db

xkevin And is there a way I can prevent an SQL injection on my code? Like using mysqli_real_escape_string? How to do it on my code structure?

As mentioned in your last thread on this forum, the surest and simplest way of protecting against sql injection is to use prepared queries and to use the php PDO extension.

You would modify the class's query() method to add an optional 2nd call time array parameter. If this parameter is empty, the code in the method would call the PDO query() method. If this parameter is not empty, the code in the method would call the PDO prepare() method, then call the PDO execute() method, supplying the array as a the call time parameter to the execute() method.

To convert the sql query into a prepared query, you would replace each php variable with a ? place-holder, removing any single-quotes and { } around the variables, then supply the variables that you just removed as the 2nd call time array parameter. The example you posted above would become -

     $sql = "INSERT INTO transactions_in(
				        transact_in_date,
					transact_in_note,
					transact_in_by_user,
					transact_in_date_added
					)
					VALUES (?,?,?,
					 NOW()
					)";

  db::query($sql,[$transact_date, $post['transact_note'], $_SESSION['user_name']]);

Any other methods in your class that execute a query, such as the select() and select_row() should not duplicate the query code, but should instead call the class's query() method.

Edit: Also, your error handling for the connection and query (and now prepare and execute) should not unconditionally output the raw error information onto the web page, as this gives hackers useful information. What you should do is use exceptions for errors (requires a single configuration statement), then let php catch any exception, where it will use its error_reporting, display_errors, and log_errors settings to control what happens with the actual error information. When learning, developing, and debugging code/queries, you would display all errors. When on a live/public server, you would log_all errors.

Thank you for your time, helping me here.

pbismad $sql = "INSERT INTO transactions_in(
transact_in_date,
transact_in_note,
transact_in_by_user,
transact_in_date_added
)
VALUES (?,?,?,
NOW()
)";

db::query($sql,[$transact_date, $post['transact_note'], $_SESSION['user_name']]);

I got an error using this code. And I mistakenly paste the wrong code here. Sorry, it should be.

  db::select($sql);

and I am using this class inside final class db{}:

 static function select($sql, $args=false){
	
	if($args['pager']){
		$limit = self::load_pager($args['pager']);
		$sql .= 'LIMIT '.$limit;
	}
	
	$result = mysqli_query(self::$db, $sql);
	if(!$result) trigger_error("MySQL SELECT error: ".mysqli_error()." using ".$sql); //error 1 

	if(@$args['explain']==true) self::explain($sql);
	
	while ($r = mysqli_fetch_array($result,MYSQLI_ASSOC)){  //error 2
			$row[] = $r;
	}

	mysqli_free_result($result); //error 3

	@array_walk_recursive($row, 'de_clean_post_recursive');
	return @$row;
  }

So now my latest code:

    $sql = "INSERT INTO transactions_in(
			        transact_in_date,
				transact_in_note,
				transact_in_by_user,
				transact_in_date_added
				)
				VALUES (?,?,?,
				 NOW()
				)";

     db::select($sql,[$transact_date, $post['transact_note'], $_SESSION['user_name']]);

And got this error:
Error 1: mysqli_error() expects exactly 1 parameter, 0 given ...
Error 2: mysqli_fetch_array() expects parameter 1 to be mysqli_result, object given ...
Error 3: mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given ...

Thank you for the concern, I am developing the program on my local machine. So nothing to worry about the errors.

Error 1: mysqli_error() expects exactly 1 parameter, 0 given ...

The other two are consequences of the first error happening and your code not doing anything to handle the possibility of error, so I'll concentrate on the first error.

Guess what? The error message, the problem it describes, and its solution are all exactly the same as when the function was mysqli_close() in your first post.

Weedpacket Adding mysqli_error(db::$db) removed the warnings. But the query is wrong. It says: You have an error in your SQL syntax ... right syntax to use near '?,?,?, NOW() )' at line 7

      $sql = "INSERT INTO transactions_in(
		        transact_in_date,
			transact_in_note,
			transact_in_by_user,
			transact_in_date_added
			)
			VALUES (?,?,?,
			 NOW()
			)";

 db::query($sql,[$transact_date, $post['transact_note'], $_SESSION['user_name']]);

//From this class rather inside final class db{}
static function query($sql)
{


	$result = mysqli_query(self::$db, $sql);
	if(!$result) trigger_error("MySQL UPDATE error: ".mysqli_error(db::$db).'<pre>'.$sql.'</pre><br>');
	#if($_SESSION['access'] == 'admin') recache($sql);
}

Do you think it there's wrong with the prepared statement?

    Well yes. You trigger an error message, but you never handle it. You ignore the fact that you had an error and carry on like everything is still fine even though you know it isn't.

    This code

       $sql = "INSERT INTO transactions_in(
    		        transact_in_date,
    			transact_in_note,
    			transact_in_by_user,
    			transact_in_date_added
    			)
    			VALUES (?,?,?,
    			 NOW()
    			)";
    
     db::query($sql,[$transact_date, $post['transact_note'], $_SESSION['user_name']]);
    

    is describing a prepared statement.

    $result = mysqli_query(self::$db, $sql);

    Mysqli_query does not run prepared statements.

    (Notice, for example, that your db::query call has two arguments $sql and [$transact_date, $post['transact_note'], $_SESSION['user_name']], but you declare that query function as only having one parameter $sql. That should be taken as a clue that you're doing something wrong.)

      xkevin I got an error using this code. And I mistakenly paste the wrong code here. Sorry, it should be.

      db::select($sql);

      NO. An INSERT query needs to use the db::query() method (which you switched back to in your last post above.) The select() method is for running SELECT queries, not an INSERT query. Also, your select() method should NOT concern itself with building part of the sql query statement needed for pagination or forming an EXPLAIN query.

      You cannot do just part of what I wrote. You must do all the things I wrote in my reply above. That includes making the coding changes to the query() method, switching to use the php PDO extension, changing the other methods so that they use the now changed query() method, and making corresponding changes to the other methods so that they will work with prepared queries. If you have questions about anything that I wrote, ask them, after you have made an effort to research and make the changes yourself.

      And if you use exceptions to handle the database errors, the php error you got for the mysqli_error() statement will disappear, because you will remove the error handling logic there is now and program execution will transfer to php's exception handling upon an error and the statements trying to fetch data and free the result won't be executed.

        pbismad You would modify the class's query() method to add an optional 2nd call time array parameter. If this parameter is empty, the code in the method would call the PDO query() method. If this parameter is not empty, the code in the method would call the PDO prepare() method, then call the PDO execute() method, supplying the array as a the call time parameter to the execute() method.

        The above paragraph lists the changes YOU have to make to the class's query() method, in addition to changing the connection code to use the php PDO extension.

        The reason I told you to change the other methods in the class so that they call the class's query() method is so that you don't repeat the lines of code needed to execute the query, thereby reducing the amount of code you have to convert.

        When you make the PDO connection, you need to -
        1) Set the character set to match the character set you are using in your database tables (utf8 is a common choice.)
        2) Set the error mode to exceptions, so that all the database statements will use exceptions for errors.
        3) Set emulated prepared queries to false, to use true prepared queries.
        4) Set the default fetch mode to assoc, so that you don't need to specify a fetch mode in every statement.

        The following is typical PDO connection code showing these settings -

        $DB_HOST = ''; // db hostname or ip address
        $DB_USER = ''; // db username
        $DB_PASS = ''; // db password
        $DB_NAME = ''; // database name
        $DB_ENCODING = 'utf8'; // db character encoding
        
        $pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // set the error mode to exceptions
        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // run real prepared queries
        $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); // set default fetch mode to assoc
        
          Write a Reply...