Can't log into php page when sql column type changed from TEXT to varchar
my login page works correctly when the column type for the password was TEXT. I updated the table content and used the sql password function on passwords, but now the format is varchar and the page will not authenticate.
below is the code:
<main>
<?php
if(isset($_POST['submit'])){
include 'databaselogin.php';
$username = ucfirst(strtolower($_POST['username']));
$username = mysqli_real_escape_string($mysqli, $username);
$password = $_POST['password'];
$sql = "SELECT *
FROM teams
WHERE name = '$username'";
$result = $mysqli->query($sql) or die($mysqli->error.__LINE__);
if($result->num_rows == 1){
$row = $result->fetch_assoc();
$hash = $row["password"];
if(password_verify($password,$hash)){
session_start();
$_SESSION["username"] = $row["name"];
header("Location: http://www.josephade.com/fyp/dashboard.php");
die();
}
}else{
//Show error incorrect password
include 'error.php';
}
mysqli_close($mysqli);
}
?>
source to share
As stated in the MySQL documentation for BLOB
and TEXT
which are related to each other.
11.4.3 BLOB and TEXT Types
If strict SQL mode is not enabled and you assign a value to a BLOB or TEXT column that exceeds the maximum column length, the value is truncated to match and a warning is generated. For truncating non-simple characters, you can raise an error (not a warning) and suppress value insertion using strict SQL mode.
which explains the loss of data.
- Hence a complete rebuild from TEXT to VARCHAR is required, as pointed out in my comment:
"you might have to rebuild your hashes, then"
Another link I found related to this:
Partial extract from this page:
Starting with MySQL 5.0.3, the maximum field length for VARCHAR fields has been increased from 255 characters to 65535 characters. This is good news because VARCHAR fields, unlike TEXT fields, are stored in string for the MyISAM storage engine (InnoDB has different characteristics). The TEXT and BLOB fields are not stored in the row - they require a separate lookup (and a potential disk read) if their column is included in the SELECT clause. In addition, including a TEXT or BLOB column in any form will force the sort to use a temporary table on disk, since the MEMORY storage engine (HEAP) is required, which is used for temporary tables.
Thus, the benefits of using a VARCHAR field instead of TEXT for columns between 255 and 65k characters seem obvious at first glance in some scenarios: potentially less disk reads (for queries including column, rows of data) and fewer records (for queries with sorts, including column).
source to share