使用MySqli预准备语句登录 [英] Login using MySqli Prepared Statement
本文介绍了使用MySqli预准备语句登录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我检查了我的代码，将IF内部的所有语句替换为echo语句，并且其工作正常.
if($rows==1){
echo "Record exists in DB";
}
我不知道为什么$result = mysqli_stmt_get_result($stmt);返回false并且我收到错误消息
I don't know why $result = mysqli_stmt_get_result($stmt); returning false and I am getting error as
警告:mysqli_fetch_array()期望参数1为mysqli_result，在第57行的E:\ xampp \ htdocs \ jq \ login_system_sql_injection_proof \ loginprocess.php中给出的布尔值
Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in E:\xampp\htdocs\jq\login_system_sql_injection_proof\loginprocess.php on line 57
我浪费了一天多的时间通过Internet搜索解决方案，但没有找到任何解决方案.如果有人帮助我，我将非常感激.我希望以程序方式解决.在此先感谢
<?php require("dbconnect.php"); ?>
<?php
$uname = $pass = "";
if($_SERVER['REQUEST_METHOD']=='POST'){
if(empty($_POST['uname'])){
header("location:loginform.php?error=".urlencode("Please enter your username"));
exit();
//$unameErr = "Please enter username";
}
else{
$uname = test_input($_POST["uname"]);
$uexp = "/^[a-zA-Z]*$/";
if(!preg_match($uexp,$uname)){
header("location:loginform.php?error=".urlencode("username should contaion only Alphabetic chars"));
exit();
//$unameErr = "only alphabets are allowed";
}
else $uname = mysqli_real_escape_string($con,$uname);
}
if(empty($_POST['pass'])){
header("location:loginform.php?error=".urlencode("Please enter your password"));
exit();
//$passErr = "Please enter password";
}
else{
$pass = test_input($_POST["pass"]);
$pass = mysqli_real_escape_string($con,$pass);
}
}
function test_input($data) {
$data = trim($data);
$data = stripslashes($data);
$data = htmlspecialchars($data);
return $data;
}
?>
<?php
$query = "SELECT *FROM user WHERE user.username = ? AND user.password = ?";
$stmt = mysqli_prepare($con,$query);
if($stmt){
mysqli_stmt_bind_param($stmt,'ss',$uname,$pass);
$run = mysqli_stmt_execute($stmt);
if($run){
mysqli_stmt_store_result($stmt);
$rows = mysqli_stmt_num_rows($stmt);
if($rows==1){
$result = mysqli_stmt_get_result($stmt);
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
echo $row['username'];
//session_start();
//$_SESSION['uid'] = $row['username'];
//header('location:welcome.php');
}
else{
header('location:loginform.php?error="Invalid username/password.. Please check and re-try login"');
}
}
else{
echo "Query Execution Failed";
}
}
else{
echo "Failed to Prepare Sql Statement";
}
?>
推荐答案
即使您可能已经找到了解决方案，我也想亲自向您展示(使用您的代码进行测试).
Even if you've maybe found a solution, I'd like to present it myself to you too (tested with your code).
我将首先告诉您，您的问题实际上是您没有使用异常处理.如果这样做，您将发现php/mysqli抛出的实际异常/错误.我建议您至少在查询数据库时始终应用异常处理.我将向您介绍封装在完整的异常处理代码中的解决方案.如果您需要进一步的解释，请随时问我.
I'll begin with telling you, that your problem is actually that you don't use exception handling. If you would have done this, you would have discovered the real exception/error thrown by your php/mysqli. I recommend you to always apply exception handling, at least when you're querying databases. I'll present you the solution(s) encapsulated in a complete exception handling code. Feel free to ask me anything, if you need further explanations.
首先，让我们看看由我编辑并带入表单的代码(仅第二部分，查询部分是相关的).
First let's see your code (just the second, querying part is relevant), edited by me and brought in my form.
<?php
/*
* Tested on table "user":
*
* ------------------------
* id username password
* ------------------------
* 1 John Smith
* 2 John Mark
*/
try {
// Your received POST values.
$uname = 'John';
$pass = 'Smith';
//---------------------------------------------------------
// Connect to db.
//---------------------------------------------------------
$con = mysqli_connect('<server>', '<user>', '<pass>', '<db>');
if (!$con) {
throw new Exception('Connect error:<br/><br/>' . mysqli_connect_errno() . ' - ' . mysqli_connect_error());
}
//---------------------------------------------------------
// Sql statement.
//---------------------------------------------------------
$query = "SELECT * FROM user WHERE user.username = ? AND user.password = ?";
//---------------------------------------------------------
// Prepare sql statement.
//---------------------------------------------------------
$stmt = mysqli_prepare($con, $query);
if (!$stmt) {
throw new Exception('The sql statement can not be prepared!');
}
//---------------------------------------------------------
// Binds variables to the prepared statement as parameters.
//---------------------------------------------------------
$bound = mysqli_stmt_bind_param($stmt, 'ss', $uname, $pass);
if (!$bound) {
throw new Exception('The variables could not be bound to the prepared statement!');
}
//---------------------------------------------------------
// Execute the prepared statement.
//---------------------------------------------------------
$executed = mysqli_stmt_execute($stmt);
if (!$executed) {
throw new Exception('The prepared statement could not be executed!');
}
//---------------------------------------------------------
// Transfers the result set from the prepared statement.
//---------------------------------------------------------
$stored = mysqli_stmt_store_result($stmt);
if (!$stored) {
throw new Exception('The result set from the prepared statement could not be transfered!');
}
//---------------------------------------------------------
// Get the number of rows in statements result set.
//---------------------------------------------------------
$rows = mysqli_stmt_num_rows($stmt);
if ($rows == 1) {
//---------------------------------------------------------
// Get the result set from the prepared statement.
//---------------------------------------------------------
$result = mysqli_stmt_get_result($stmt);
if (!$result) {
throw new Exception(mysqli_error($con));
//----------------------------------------------------------------
// OR:
//----------------------------------------------------------------
// Get a list of errors from the last command executed.
// Each error displayed as an associative array containing the
// errno, error, and sqlstate.
// Because array returned, then no exception thrown, just display.
//----------------------------------------------------------------
// $errorList = mysqli_error_list($con);
// echo '<pre>' . 'Error retrieving result set:<br/></br>' . print_r($errorList, true) . '</pre>';
// exit();
//----------------------------------------------------------------
}
//---------------------------------------------------------
// Read the result set.
//---------------------------------------------------------
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
if (!isset($row)) {
echo 'No records returned!';
exit();
}
echo 'Returned username is: ' . $row['username'];
} else {
echo 'Invalid username/password. Please check and retry login';
}
//-----------------------------------------------------------
// Frees stored result memory for the given statement handle.
//-----------------------------------------------------------
mysqli_stmt_free_result($stmt);
//---------------------------------------------------------
// Close db connection.
//---------------------------------------------------------
$closed = mysqli_close($con);
if (!$closed) {
throw new Exception('The database connection can not be closed!');
}
} catch (Exception $exception) {
echo '<pre>' . print_r($exception, true) . '</pre>';
exit();
}
如果您运行的代码是同时使用 mysqli_stmt_store_result() 和 mysqli_stmt_get_result() 的，则您将获得
If you run this code which is using mysqli_stmt_store_result() together with mysqli_stmt_get_result() you'll get the
Exception Object
(
[message:protected] => Commands out of sync; you can't run this command now
[string:Exception:private] =>
[code:protected] => 0
[file:protected] => [...]/index.php
[line:protected] => 63
[trace:Exception:private] => Array
(
)
[previous:Exception:private] =>
[xdebug_message] => ( ! ) Exception: Commands out of sync; you can't run this command now in [...]/index.php on line 63
Call Stack
#TimeMemoryFunctionLocation
10.2170368808{main}( ).../index.php:0
)
或以这种形式(请参阅我的代码):
Or in this form (see my code):
Error retrieving result set:
Array
(
[0] => Array
(
[errno] => 2014
[sqlstate] => HY000
[error] => Commands out of sync; you can't run this command now
)
)
此异常( Commands out of sync; you can't run this command now )指出，您不能同时运行两个查询.有关详细信息，请参见此答案例子.
您在问题中显示的警告实际上是在告诉您效果:您打来的电话没有得到任何mysqli_result
The warning you showed in the question is telling you actually the effect: that you are not getting any mysqli_result from your call to
$result = mysqli_stmt_get_result($stmt);
因此，您的
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
接收 FALSE 作为参数(用于$result)并向您提出警告
receives FALSE as argument (for $result) and raises (to you) the warning
警告:mysqli_fetch_array()期望参数1为mysqli_result， 布尔值 E:\ xampp \ htdocs \ jq \ login_system_sql_injection_proof \ loginprocess.php 在第57行上
Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in E:\xampp\htdocs\jq\login_system_sql_injection_proof\loginprocess.php on line 57
为了解决这个问题，我给您两个解决方案.
In order to resolve this, I'll give you two solutions.
仅将 mysqli_stmt_get_result() 与 mysqli_fetch_array() 一起使用:
Use only mysqli_stmt_get_result() with mysqli_fetch_array():
<?php
/*
* Tested on table "user":
*
* ------------------------
* id username password
* ------------------------
* 1 John Smith
* 2 John Mark
*/
try {
$uname = 'John';
$pass = 'Smith';
//---------------------------------------------------------
// Connect to db.
//---------------------------------------------------------
$con = mysqli_connect('<server>', '<user>', '<pass>', '<db>');
if (!$con) {
throw new Exception('Connect error:<br/><br/>' . mysqli_connect_errno() . ' - ' . mysqli_connect_error());
}
//---------------------------------------------------------
// Sql statement.
//---------------------------------------------------------
$query = "SELECT * FROM user WHERE user.username = ? AND user.password = ?";
//---------------------------------------------------------
// Prepare sql statement.
//---------------------------------------------------------
$stmt = mysqli_prepare($con, $query);
if (!$stmt) {
throw new Exception('The sql statement can not be prepared!');
}
//---------------------------------------------------------
// Binds variables to the prepared statement as parameters.
//---------------------------------------------------------
$bound = mysqli_stmt_bind_param($stmt, 'ss', $uname, $pass);
if (!$bound) {
throw new Exception('The variables could not be bound to the prepared statement!');
}
//---------------------------------------------------------
// Execute the prepared statement.
//---------------------------------------------------------
$executed = mysqli_stmt_execute($stmt);
if (!$executed) {
throw new Exception('The prepared statement could not be executed!');
}
//---------------------------------------------------------
// Get the result set from the prepared statement.
//---------------------------------------------------------
$result = mysqli_stmt_get_result($stmt);
if (!$result) {
throw new Exception(mysqli_error($con));
}
//---------------------------------------------------------
// Get the number of rows in statements result set.
//---------------------------------------------------------
$rows = mysqli_num_rows($result);
if ($rows == 1) {
//---------------------------------------------------------
// Read the result set.
//---------------------------------------------------------
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
if (!isset($row)) {
echo 'No records returned!';
exit();
}
echo 'Returned username is: ' . $row['username'];
} else {
echo 'Invalid username/password. Please check and retry login';
}
//-----------------------------------------------------------
// Frees stored result memory for the given statement handle.
//-----------------------------------------------------------
mysqli_stmt_free_result($stmt);
//---------------------------------------------------------
// Close db connection.
//---------------------------------------------------------
$closed = mysqli_close($con);
if (!$closed) {
throw new Exception('The database connection can not be closed!');
}
} catch (Exception $exception) {
echo '<pre>' . print_r($exception, true) . '</pre>';
exit();
}
解决方案2:
与 mysqli_stmt_bind_result() 和 mysqli_stmt_fetch() 一起使用 mysqli_stmt_store_result() :
Solution 2:
Use mysqli_stmt_store_result() together with mysqli_stmt_bind_result() and mysqli_stmt_fetch():
<?php
/*
* Tested on table "user":
*
* ------------------------
* id username password
* ------------------------
* 1 John Smith
* 2 John Mark
*/
try {
$uname = 'John';
$pass = 'Smith';
//---------------------------------------------------------
// Connect to db.
//---------------------------------------------------------
$con = mysqli_connect('<server>', '<user>', '<pass>', '<db>');
if (!$con) {
throw new Exception('Connect error:<br/><br/>' . mysqli_connect_errno() . ' - ' . mysqli_connect_error());
}
//---------------------------------------------------------
// Sql statement.
//---------------------------------------------------------
$query = "SELECT * FROM user WHERE user.username = ? AND user.password = ?";
//---------------------------------------------------------
// Prepare sql statement.
//---------------------------------------------------------
$stmt = mysqli_prepare($con, $query);
if (!$stmt) {
throw new Exception('The sql statement can not be prepared!');
}
//---------------------------------------------------------
// Binds variables to the prepared statement as parameters.
//---------------------------------------------------------
$bound = mysqli_stmt_bind_param($stmt, 'ss', $uname, $pass);
if (!$bound) {
throw new Exception('The variables could not be bound to the prepared statement!');
}
//---------------------------------------------------------
// Execute the prepared statement.
//---------------------------------------------------------
$executed = mysqli_stmt_execute($stmt);
if (!$executed) {
throw new Exception('The prepared statement could not be executed!');
}
//---------------------------------------------------------
// Transfers the result set from the prepared statement.
//---------------------------------------------------------
$stored = mysqli_stmt_store_result($stmt);
if (!$stored) {
throw new Exception('The result set from the prepared statement could not be transfered!');
}
//---------------------------------------------------------
// Get the number of rows in statements result set.
//---------------------------------------------------------
$rows = mysqli_stmt_num_rows($stmt);
if ($rows == 1) {
//---------------------------------------------------------
// Bind result set columns to variables.
//---------------------------------------------------------
$bound = mysqli_stmt_bind_result($stmt, $resId, $resUsername, $resPassword);
if (!$bound) {
throw new Exception('The result set columns could not be bound to the variables');
}
//--------------------------------------------------------------------
// Fetch results from the prepared statement into the bound variables.
//--------------------------------------------------------------------
while (mysqli_stmt_fetch($stmt)) {
echo 'Successfully returned data:<br/><br/>';
echo 'ID is: ' . $resId . '<br/>';
echo 'Username is: ' . $resUsername . '<br/>';
echo 'Password is: ' . $resPassword . '<br/>';
}
} else {
echo 'Invalid username/password. Please check and retry login';
}
//-----------------------------------------------------------
// Frees stored result memory for the given statement handle.
//-----------------------------------------------------------
mysqli_stmt_free_result($stmt);
//---------------------------------------------------------
// Close db connection.
//---------------------------------------------------------
$closed = mysqli_close($con);
if (!$closed) {
throw new Exception('The database connection can not be closed!');
}
} catch (Exception $exception) {
echo '<pre>' . print_r($exception, true) . '</pre>';
exit();
}
除了您的代码外，我还添加了异常处理和mysqli_stmt_free_result($stmt)函数以释放结果集占用的内存.
In addition to your code I've added exception handling and the mysqli_stmt_free_result($stmt) function to free the memory occupied by the result set.
祝你好运.
这篇关于使用MySqli预准备语句登录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
