Member Avatar for samsons17

hi i'm working on codeigniter for now.and i got some problem with the user login system.

basically user can login using his/her email address, and then the login system will retrieve the user corresponding "username" base on the email address entered.This username will then be stored as session and will going to be used for all of the user's activities on the site.

So, for login system, after the user has entered the details of his own, the validate_credentials() function will be called :

function validate_credentials()
	{
		$this->load->model('membership_model');
		$query = $this->membership_model->validate();

		if($query) // if the user's credentials validated...
		{
                     $sql = $this->db->query("SELECT * FROM membership WHERE email_address=".$this->input->post('email'));
		    if ($sql->num_rows() > 0)
                    {
                    $row = $query->row_array();
                    $username = $row['username'];
                    }

                     $data = array(
				'username' => $username,
                                'is_logged_in' => true
			);
			$this->session->set_userdata($data);
			redirect('site/home/'.$username);
		}
		else // incorrect username or password
		{
			$this->index();
		}
	}

and this function will interact with the validate() method in the membership_model to check weather or not the details entered is correct.this is the validate() method :

function validate()
	{
		$this->db->where('email_address', $this->input->post('email'));
		$this->db->where('password', md5($this->input->post('password')));
		$query = $this->db->get('membership');

		if($query->num_rows == 1)
		{
			return true;
		}

	}

i get this error when try to login :

A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@yahoo.com' at line 1

SELECT * FROM membership WHERE email_address=

Filename: C:\xampp\htdocs\project\system\database\DB_driver.php

Line Number: 330


So whats the mistake that i have done?any helps guys?
thanks :)

  • 3 Contributors
  • 7 Replies
  • 2K Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by kurtopia

Dani AI

Generated

The SQL 1064 is from not quoting the email in your raw query, as and said. But there are two deeper bugs making this harder to diagnose: (1) in your model you use $query->num_rows instead of the method $query->num_rows(), so the check never behaves correctly; and (2) in the controller you later call $query->row_array() even though $query is just the boolean returned from validate() (you meant to use the result set variable).

A cleaner fix is to let the model return the user row (so you do not need a second query or manual quoting), and then set the session from that:

// model
public function validate($email, $password)
{
    return $this->db->select('username')
        ->where('email_address', $email)
        ->where('password', md5($password)) // for legacy code; prefer password_hash later
        ->limit(1)
        ->get('membership')
        ->row_array();
}

// controller
$user = $this->membership_model->validate(
    $this->input->post('email', true),
    $this->input->post('password', true)
);

if ($user) {
    $this->session->set_userdata([
        'username' => $user['username'],
        'is_logged_in' => true
    ]);
    redirect('site/home/' . rawurlencode($user['username']));
} else {
    // show login error
}

Notes:

  • If you must write raw SQL, bind parameters: $this->db->query('SELECT username FROM membership WHERE email_address = ?', [$email]); That handles quoting safely.
  • Use $query->num_rows() when counting results.
  • For modern apps, replace md5 with password_hash/password_verify and store only the hash.
  • Make email_address unique in the DB to guarantee a single match.

Recommended Answers

All 7 Replies

Member Avatar for R0bb0b

try putting single quotes around the email address. Also, make sure that you are at least using mysql_real_escape_string for all human interaction into the database.

Member Avatar for kurtopia

yeah try putting quotes in the value of email address so that the query will treat it as the value of the field email_address..

Member Avatar for samsons17

it didnt work..plus i believe this is not the problem for this..

Member Avatar for R0bb0b

Error number 1064 is a syntax error. The only syntax error that you have in that query is trying to pass a string as an integer. Mysql has no idea how to parse that query without single quotes around your strings. Sure, it just might possibly work in some cases but mysql doesn't know what to do with that particular string.

Member Avatar for samsons17

Error number 1064 is a syntax error. The only syntax error that you have in that query is trying to pass a string as an integer. Mysql has no idea how to parse that query without single quotes around your strings. Sure, it just might possibly work in some cases but mysql doesn't know what to do with that particular string.

Ouh..but where should i put those quotes? is it this what you meant? :

$sql = $this->db->query("SELECT * FROM membership WHERE 'email_address'=".$this->input->post('email'));

if it it like this, unfortunately it turns out to be another systax error
that i got :(

Member Avatar for R0bb0b

like this:

$sql = $this->db->query("SELECT * FROM membership WHERE email_address='".$this->input->post('email')."'");
Member Avatar for kurtopia

yeah R0bb0b is correct..... so that the query will treat the email address value as a string.....
php Syntax (Toggle Plain Text)
$sql = $this->db->query("SELECT * FROM membership WHERE email_address='".$this->input->post('email')."'");

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.