error in SQL syntax?

[eluser]anna16[/eluser]
guys

I created a model class called membership.php
and inside it, it has check_email() method.

Code:

function check_email()
  {
    $query = $this->db->query("SELECT email FROM user WHERE email='$this->input->post('email')");
        return $query->result();
  }

I guess this method has an error, can you spot the not of it?

thanks in advanced.

[eluser]anna16[/eluser]
I think this part below is not right,

Code:

email='$this->input->post('email')

It should capture the data from a form.
Is that code right?

[eluser]InsiteFX[/eluser]
Hi anna,

Your missing a single qoute on the end...

Code:

$query = $this->db->query("SELECT email FROM user WHERE email='$this->input->post('email')'");

InsiteFX

[eluser]Twisted1919[/eluser]
And yes, beside this, xss and sql injection vulnerability

[eluser]anna16[/eluser]
@twisted919

can you share your knowledge to me, how do i make that xss and sql injection safe?
can you show some snippet codes.

thanks in advanced.

[eluser]Narkboy[/eluser]
Ok - Twisted1919s cryptic answer aside - you need to be certain that anything coming from the broswer is checked and is exactly what you expect.

$this->input->post is a good way to access POST vars as it does some of the work for you by checking for XSS attackes.

However - consider this:

Code:

$_POST['email'] = '';
$_POST['email'] = '""; DELETE FROM USER;';

The first will return no records (unless you allow users without email addresses) so $query->result() will throw an error. The second won't actually work because you're using Active Record, but you can see the harm that could be done. There are more subtle sql injection techniques out there.

To be certain that you're getting what you want:

1st - check that $_POST['email'] is set, and has a non-null value:

Code:

if ( $this->input->post('email') == '' ) {
    // Problem!
};

Next, check that the value is a valid email address - as far as I know there is no way to inject sql using a string that validates as an email. Though - that would bepretty cool!

You can either use CI Validation or write your own email validation function using regex. I'd suggest using CI Validation though - why re-invent the wheel?



/B


**Edit**

The function above takes an email as input, checks the database for a matching row with that email, and then returns - the same email. Seems redundent.

Are you trying to work out if you have a record of a user with that email? I'd suggest this:

Code:

function check_email( $email = '' ) {
    
    if ( $email == '' ) {
        // Nothing passed through:
        return FALSE;
    }

    // Here, we use a custom email validation function to make sure it's a valid email:
// The custom function returns FALSE if the email is invalid, TRUE otherwise.
    if ( ! $this->my_custom_email_validator_function( $email ) ) {
        // $email is not actually an email address!
        return FALSE;
    }

    $query = $this->db->get_where('user_table' , array( 'email' => $email ) );

    if ( $query->num_rows() == 1 ) {
        // This checks for a matching record - if one record is returned, we're ok.
        return TRUE;
    } else {
        // This code fires if we get 0 or 2+ recdords - a problem in either case.
        return FALSE;
    }
}

To call the function from a controller, use:

Code:

if ( $this->model_name->check_email( $this->input->post('email') ) ) {
    // Yay - we have a record with this email address registered.
} else {
    // Oops - this email is not registered in the user table.
}

The only thing missing here (apart from the code for email validation) is error reporting - the check_email function returns FALSE if nothing is sent, if the email is not valid or if the email is unrecognised. You should drop in code to either log or report the outcome if it's important to know where the issue is. For things like this I usually don't worry too much - either we know the email or we don't.

/B

[eluser]flaky[/eluser]
why not do this

Code:

$this->db->select('email');
$this->db->where('email', $this->input->post('email'));
return $this->db->get('user')->row_array();

[eluser]anna16[/eluser]
thanks again narkboy

[eluser]InsiteFX[/eluser]
The second optional parameter lets you run the data through the XSS filter. It's enabled by setting the second parameter to boolean TRUE;

Code:

$this->input->post('some_data', TRUE);

// form validation
$this->form_validation->set_rules('email', 'Email', 'trim|required|valid_email|xss_clean');

InsiteFX

[eluser]anna16[/eluser]
thanks flaky I'll try your suggestion.

by the way can you guys explain I'm having 3 errors,
http://coder9.com/ci172x/index.php/membe...er_account

I'm confused which error should i fix first?

thanks in advanced.