SunshinePHP 2020

The mysqli class

(PHP 5, PHP 7)

Introduction

Represents a connection between PHP and a MySQL database.

Class synopsis

mysqli {
/* Properties */
int $errno;
string $error;
string $client_info;
string $host_info;
string $server_info;
string $info;
string $sqlstate;
/* Methods */
__construct ([ string $host = ini_get("mysqli.default_host") [, string $username = ini_get("mysqli.default_user") [, string $passwd = ini_get("mysqli.default_pw") [, string $dbname = "" [, int $port = ini_get("mysqli.default_port") [, string $socket = ini_get("mysqli.default_socket") ]]]]]] )
autocommit ( bool $mode ) : bool
change_user ( string $user , string $password , string $database ) : bool
character_set_name ( void ) : string
close ( void ) : bool
commit ([ int $flags = 0 [, string $name ]] ) : bool
connect ([ string $host = ini_get("mysqli.default_host") [, string $username = ini_get("mysqli.default_user") [, string $passwd = ini_get("mysqli.default_pw") [, string $dbname = "" [, int $port = ini_get("mysqli.default_port") [, string $socket = ini_get("mysqli.default_socket") ]]]]]] ) : void
debug ( string $message ) : bool
dump_debug_info ( void ) : bool
get_charset ( void ) : object
get_client_info ( void ) : string
get_connection_stats ( void ) : bool
mysqli_stmt::get_server_info ( void ) : string
init ( void ) : mysqli
kill ( int $processid ) : bool
more_results ( void ) : bool
multi_query ( string $query ) : bool
next_result ( void ) : bool
options ( int $option , mixed $value ) : bool
ping ( void ) : bool
public static poll ( array &$read , array &$error , array &$reject , int $sec [, int $usec = 0 ] ) : int
prepare ( string $query ) : mysqli_stmt
query ( string $query [, int $resultmode = MYSQLI_STORE_RESULT ] ) : mixed
real_connect ([ string $host [, string $username [, string $passwd [, string $dbname [, int $port [, string $socket [, int $flags ]]]]]]] ) : bool
escape_string ( string $escapestr ) : string
real_escape_string ( string $escapestr ) : string
real_query ( string $query ) : bool
public reap_async_query ( void ) : mysqli_result
public refresh ( int $options ) : bool
rollback ([ int $flags = 0 [, string $name ]] ) : bool
rpl_query_type ( string $query ) : int
select_db ( string $dbname ) : bool
send_query ( string $query ) : bool
set_charset ( string $charset ) : bool
set_local_infile_handler ( mysqli $link , callable $read_func ) : bool
ssl_set ( string $key , string $cert , string $ca , string $capath , string $cipher ) : bool
stat ( void ) : string
store_result ([ int $option ] ) : mysqli_result
}

Table of Contents

add a note add a note

User Contributed Notes 1 note

up
1
Vasiliy Makogon
2 years ago
The main drawbacks of all libraries for working with the database in PHP are:

1. Verbosity

    To prevent SQL injection, developers have two ways:

        - Use prepared requests.
        - Manually escaping parameters going into the body of an SQL query. String parameters are run via mysqli_real_escape_string (), and the expected numeric parameters lead to the corresponding types - int and float.

    Both approaches have enormous disadvantages:

        - Prepared queries are awfully verbose. To use "out of the box" the abstraction of PDO or the extension of mysqli, without aggregating all methods to get data from the DBMS is simply impossible - to get the value from the table you need to write a minimum of 5 lines of code! And so for every request!
        - Manual screening of parameters going to the body of an SQL query is not even discussed. A good programmer is a lazy programmer. Everything should be as automated as possible.

2. Failed to get SQL query for debugging

    To understand why the SQL-query does not work in the program, it needs to be debugged - to find either a logical or a syntactic error. To find the error, it is necessary to "see" the SQL query itself, to which the database was "bent", with parameters set in its body. Those. To have a fully-formed SQL.
    If the developer uses the PDO, with the requests being prepared, then this is done ... IMPOSSIBLE! No maximum convenient mechanisms for this in their native libraries are FOREWORD. It remains either to pervert, or climb into the database log.

Solution: Database - class for working with MySql - github.com/Vasiliy-Makogon/Database

     Eliminates the verbosity - instead of 3 or more lines of code for execution of one query when using the "native" library, you write only 1!
     Shields all parameters going to the body of the request, according to the specified type of placeholders - reliable protection against SQL injections.
     It does not replace the functionality of the "native" mysqli adapter, but simply complements it.

What are placeholders?

Placeholders are special typed markers that are written in the SQL query string instead of explicit values (query parameters). And the values themselves are passed "later", as subsequent arguments to the main method that executes the SQL query:

<?php
// Connect to the DBMS and get the Database_Mysql object
// Database_Mysql - "wrapper" over the "native" mysqli object
$db = Database_Mysql::create ("localhost", "root", "password")
      
// Select the database
      
-> setDatabaseName ("test")
      
// Select the character set
      
-> setCharset ("utf8");

// Get the result object Database_Mysql_Statement
// Database_Mysql_Statement - "wrapper" over the "native" object mysqli_result
$result = $db->query ("SELECT * FROM` users` WHERE `name` = '? S' AND` age` =? I "," Basil ", 30);

// Get the data (in the form of an associative array, for example)
$data = $result->fetch_assoc();

// Does the query not work? No problem - print it out:
echo $db->getQueryString();
?>

SQL query parameters passed through the placeholders system are processed by special screening functions, depending on the type of placeholders.

More information see on github.com/Vasiliy-Makogon/Database
To Top