[SOLVED] Syntax Error: You have an error in your SQL syntax;

[eluser]riwakawd[/eluser]
I would like to use the code I have below but keeps on saying You have an error in your SQL syntax. I know about sha1 one not beaning to good I have other added security that not on here.

Code:

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 'admin'', salt = ''*****'', password = '' ***** ' at line 1

INSERT INTO `user` SET user_id = '1', user_group_id = '1', username = ''admin'', salt = ''*****'', password = ''********'', `status` = '1', email = ''******'', date_added = NOW()

Code:

public function database() {

$file = APPPATH . 'ci.sql';
            
            if (!file_exists($file)) {
                  exit('Could not load sql file: ' . $file);
            }
            
            $lines = file($file);
            
            if ($lines) {
                  $sql = '';

                  foreach($lines as $line) {
                        if ($line && (substr($line, 0, 2) != '--') && (substr($line, 0, 1) != '#')) {
                              $sql .= $line;
  
                              if (preg_match('/;\s*$/', $line)) {
                                    $sql = str_replace("DROP TABLE IF EXISTS `", "DROP TABLE IF EXISTS `" . $this->input->post('db_prefix'), $sql);
                                    $sql = str_replace("CREATE TABLE IF NOT EXISTS `", "CREATE TABLE IF NOT EXISTS `" . $this->input->post('db_prefix'), $sql);
                                    $sql = str_replace("CREATE TABLE `", "CREATE TABLE `" . $this->input->post('db_prefix'), $sql);
                                    $sql = str_replace("INSERT INTO `", "INSERT INTO `" . $this->input->post('db_prefix'), $sql);
                                    
                                    $this->db->query($sql);
      
                                    $sql = '';
                              }
                        }
                  }

            }



$data['db_prefix'] = $this->input->post('db_prefix');
$data['username'] = $this->input->post('username');
$data['password'] = $this->input->post('password');
$data['email'] = $this->input->post('email');

$this->db->query("DELETE FROM `" . $data['db_prefix'] . "user` WHERE user_id = '1'");

$this->db->query("
    INSERT INTO
       `" . $data['db_prefix'] . "user`
    SET
         user_id       = '1',
         user_group_id = '1',
         username      = '" . $this->db->escape($data['username']) . "',  
         salt          = '" . $this->db->escape($salt = substr(md5(uniqid(rand(), true)), 0, 9)) . "',
         password      = '" . $this->db->escape(sha1($salt . sha1($salt . sha1($data['password'])))) . "',

         `status`      = '1',

         email         = '" . $this->db->escape($data['email']) . "',
         date_added    = NOW()

");

}

[eluser]rm_beginner[/eluser]
put your sql query into a variable.

$sql="your sql statement........."
then
die($sql);

then give me the output.


before you run... $this->db->query($sql);

[eluser]riwakawd[/eluser]
[quote author="rm_beginner" date="1403705673"]put your sql query into a variable.

$sql="your sql statement........."
then
die($sql);

then give me the output.


before you run... $this->db->query($sql);
[/quote]

I have updated my post

[eluser]treenef[/eluser]
Why don't you use active record. And use PHP crypt(). It's the best way to hash passwords now.

[eluser]riwakawd[/eluser]
[quote author="treenef" date="1403707645"]Why don't you use active record. And use PHP crypt(). It's the best way to hash passwords now.[/quote]

Because I could never get codeighter bcrypt or any thing like that to work I am over it spent week and a bit on it no luck so doing it my way plus I have extra security features any way.

[eluser]CroNiX[/eluser]
Looking at the query it should be evident that there are double single quotes around the things you are escaping. escape() adds the quotes, so you don't manually need to.

Code:

username = ''admin''

etc

[eluser]riwakawd[/eluser]
[quote author="CroNiX" date="1403709017"]Looking at the query it should be evident that there are double single quotes around the things you are escaping. escape() adds the quotes, so you don't manually need to.

Code:

username = ''admin''

etc[/quote]

Is there away to be able to use db escape but remove the quotes

[eluser]CroNiX[/eluser]
You're manually adding them in your query. Don't.

[eluser]treenef[/eluser]

plus I have extra security features any way.

Wrong using md5 nowadays is just plain retarded and the way you salt it makes no noticeable difference. Crypt() has never been easier to use, you just need to make sure you have a reasonably up to date version of php.

Code:

if (crypt($Password1, $HashedPassword) == $HashedPassword) {
echo "Hashed Password matched Password1";
} else {
echo "Hashed Password didn't match Password1";
}

That there is really all you need and you're a good as you need in terms of security. See link for details.

http://php.ss23.geek.nz/2011/01/12/Using-crypt.html

The other thing that pains me when reading your code is you blatant refusal of using active record. Granted sometimes when you have complicated queries that have lots of joins and sub queries you can't use active record.

But your queries are dead simple, not only that you're causing yourself headaches by trying to add quotes here and there. Once again, I can't state it enough 'USE active record' and save yourself the embarrassment of post like these. I hope that helped.

[eluser]InsiteFX[/eluser]

Code:

// Create an sql command structure for creating a table

$tableCreate = "CREATE TABLE members (
id int(11) NOT NULL auto_increment,
username varchar(255) NOT NULL,
email varchar(255) NOT NULL,
password varchar(255) NOT NULL,
website varchar(255) NULL,
account_type enum('a','b','c') NOT NULL default 'a',
PRIMARY KEY (id),
UNIQUE KEY email (email)
)";