Laravel登录异常：Auth::login无法持久化，Auth::attempt触发MariaDB错误

问题概述 #

• 使用Auth::login($user, true)可完成登录，但无法保持登录状态
• 直接传入Eloquent模型到Auth::attempt($user)时，触发MariaDB语法错误
• 使用Auth::attempt(['email' => $user['email'], 'password' => $user['password']])始终返回null，即使测试存储明文密码的用户也是如此

登录流程与验证代码 #

用户访问页面输入邮箱和reCAPTCHA，系统校验用户存在后发送邮箱验证码，用户输入验证码后进入以下验证逻辑：

public function validateCode(Request $request)
{
    $email = $request->email;

    // 获取用户
    $user = User::whereEmail($email);

    if ($user->count() > 0) {
        $validateCode = $request->input('validateCode');

        // 获取数据库中存储的验证码
        $user = $user->first();
        $userCode = $user['codConfirm'];

        // 验证验证码
        if ($validateCode == $userCode) {
            Auth::login($user, true);
            
            if(Auth::check())
                return redirect()->route('home');
            else
                return redirect()->action('Auth\LoginController@index');
        } else {
            return view('confirmar-usuario', ['email' => $email]);
        }
    } 
    else {
        flash('Usuário inválido')->error();

        return view('login');
    }
}

Auth::attempt的错误场景 #

1. 传入模型实例触发MariaDB语法错误：

if(Auth::attempt($user)){
...
}

2. 传入凭证数组返回null：

if (Auth::attempt(['email' => $user['email'], 'password' => $user['password']])) {
    ...
}

MariaDB错误信息 #

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`' at line 1 (SQL: select * from `users` where `*fillable` in (name, email, password, codConfirm, office, people_type, photo, status) and `*hidden` in (remember_token) and `*casts` in (datetime) and `*connection` = mysql and `*table` = users and `*primaryKey` = id and `*keyType` = int and `incrementing` = 1 and 0 = 1 and 0 = 1 and `*perPage` = 15 and `exists` = 1 and `wasRecentlyCreated` = 0 and `*attributes` in (1, Thiago Scomparin, scompa@cantacom.com.br, ?, $2y$10$Fxq0kHJBXLQLCFeDOOtroO5CtFOX38OwwkPlguOJq3bDsOonp37RS, active, ?, 2023-02-16 13:11:19, 2023-02-17 11:15:45, SCpXC2, Escritório Cantacom, Desenvolvedor, users/o5GFWy1woLIni1x0n5J7e0SlOgvsj1y5yyTjznnO.jpg) and `*original` in (1, Thiago Scomparin, scompa@cantacom.com.br, ?, $2y$10$Fxq0kHJBXLQLCFeDOOtroO5CtFOX38OwwkPlguOJq3bDsOonp37RS, active, ?, 2023-02-16 13:11:19, 2023-02-17 11:15:45, SCpXC2, Escritório Cantacom, Desenvolvedor, users/o5GFWy1woLIni1x0n5J7e0SlOgvsj1y5yyTjznnO.jpg) and 0 = 1 and 0 = 1 and 0 = 1 and `*dateFormat` is null and 0 = 1 and 0 = 1 and 0 = 1 and 0 = 1 and 0 = 1 and `timestamps` = 1 and 0 = 1 and `*guarded` in (*) and `*rememberTokenName` = remember_token and `*accessToken` is null limit 1)

User模型代码 #

class User extends Authenticatable
{
    use HasApiTokens, Notifiable;

    /**
     * 可批量赋值的属性
     *
     * @var array
     */
    protected $fillable = [
        'name',
        'email',
        'password',
        'codConfirm',
        'office',
        'people_type',
        'photo',
        'status'
    ];

    /**
     * 数组中应隐藏的属性
     *
     * @var array
     */
    protected $hidden = [
        // 'password',
        'remember_token',
    ];

    /**
     * 应转换为本机类型的属性
     *
     * @var array
     */
    protected $casts = [
        'email_verified_at' => 'datetime',
    ];

    public static function status()
    {
        return [
            'active' => 'Ativo',
            'disabled' => 'Desativado'
        ];
    }
}

Auth配置（config/auth.php） #

'guards' => [
        'web' => [
            'driver' => 'session',
            'provider' => 'users',
        ],

        'api' => [
            'driver' => 'passport',
            'provider' => 'users',
            'hash' => false,
        ],
    ],

'providers' => [
        'users' => [
            'driver' => 'eloquent',
            'model' => App\User::class,
        ],
    ],

相关版本信息 #

"php": "^7.2.5|^8.0",
"laravel/framework": "^7.29",

问题分析与解决方案 #

1. Auth::login无法保持登录状态的修复 #

Auth::login($user, true)的$remember参数为true时，需要满足两个前提：

• 数据库users表存在remember_token字段（类型为varchar(100)，允许为空），若不存在则执行迁移添加：

  Schema::table('users', function (Blueprint $table) {
      $table->rememberToken();
  });
  

• 会话配置正确：检查config/session.php，本地开发环境建议配置：

  'domain' => null,
  'secure' => env('SESSION_SECURE_COOKIE', false),
  'same_site' => 'lax',
  

  确保会话存储目录（file驱动）有写入权限，或会话表（database驱动）已创建。

2. Auth::attempt的错误原因与修正 #

• 传入模型实例的错误：Auth::attempt()要求传入关联数组形式的登录凭证，而非Eloquent模型。直接传模型会导致Laravel将模型的内部属性（如$fillable、$hidden）作为查询条件，生成无效SQL。
• 凭证数组返回null的问题：Auth::attempt()会自动对密码进行哈希校验，若传入数据库中存储的哈希密码，会导致二次哈希验证失败。而你的场景是验证码登录，不需要密码验证，因此Auth::login()才是正确选择，无需使用Auth::attempt()。

3. 验证码登录流程优化建议 #

添加验证码过期时间、验证后清空验证码，提升安全性：

public function validateCode(Request $request)
{
    $request->validate([
        'email' => 'required|email',
        'validateCode' => 'required|string|max:6',
    ]);

    $user = User::where('email', $request->email)->first();

    if (!$user) {
        flash('Usuário inválido')->error();
        return view('login');
    }

    // 假设已添加codConfirm_expires字段存储验证码过期时间
    if ($request->validateCode !== $user->codConfirm || now()->greaterThan($user->codConfirm_expires)) {
        flash('验证码无效或已过期')->error();
        return view('confirmar-usuario', ['email' => $request->email]);
    }

    Auth::login($user, true);
    // 清空验证码
    $user->update(['codConfirm' => null, 'codConfirm_expires' => null]);

    return redirect()->route('home');
}

内容的提问来源于stack exchange，提问作者Gabriel Edu