Preface

After learning basic SQL statements, we need to add some parameters to the statements. If we use string concatenation directly, it is very easy to cause SQL injection, which affects data security. Therefore, we now need to preprocess SQL statements, setting the places to be filled as variables, so that no matter what, it will not be vulnerable to SQL injection.

Connection

1
2
3
4
5
6
7
8
9
10
11
12
$mysql_server_name = '127.0.0.1';
$mysql_username = 'root';
$mysql_password = '123456';
$mysql_database = 'sjk';
$conn = mysqli_connect($mysql_server_name, $mysql_username, $mysql_password, $mysql_database);
// Database connection error prompt
if (mysqli_connect_errno($conn)) { 
    echo "<script>alert('Unable to connect to the database')</script>";
    exit();
}
mysqli_query($conn, "set names utf8"); // Database encoding format
mysqli_set_charset($conn, "utf8"); // Set default client character set.

Construction

When constructing SQL statements, we use ? to fill in the places for variables.

1
2
$sql = "SELECT * FROM `data` where `code`=? limit 1";
$stmt = mysqli_prepare($link, $sql);

Binding Parameters

We bind the parameters to be filled to the SQL statement. Note the s in the first line, where s represents one string. If we want to bind multiple parameters at the same time, we need to write it as sss, with as many s as there are parameters.

1
2
mysqli_stmt_bind_param($stmt, 's', $value);
mysqli_stmt_execute($stmt);

Retrieving Results

If it is a query statement, we need to retrieve the results obtained from the query. To get a specific result directly, use ['name'].

1
2
$result = mysqli_stmt_get_result($stmt);
$row = mysqli_fetch_array($result);