Unable to bind values in sql query

[fear126]

i need help i created a attachValues function which will attach values to sql query and execute it.

I am using SQLITE database using PDO.

 

Here is my code : 
 

private function attachValues($sqlQuery, $params = array()) {
		if ($this->_query = $this->_database->prepare($sqlQuery)) {
			$binder = 1;
			if (count($params)) {
				foreach ($params as $para){
					$this->_query->bindValue($binder, $para);
					$binder++;
				}
			}
			if ($this->_query->execute()) {
				
				return true;
			} else {
				return false;
			}
		}
		return $this;
	}

public function addNewUser($data = array()){
		if (count($data)) {
			$keys   = array_keys($data);
			$values = '';
			$binder = 1;
			foreach ($data as $field) {
				$values .= '?';
				if ($binder < count($data)) {
					$values .= ', ';
				}
				$binder++;
			}
			$sqlQuery = "insert into `users` (`" . implode('`, `', $keys) . "`) VALUES ({$values})";
			var_dump($sqlQuery);
			if ($this->attachValues($sqlQuery, $data) != false) {
				return true;
			}
		}
		return false;
	}

Error Message : with query dump
string(81) "insert into `users` (`name`, `username`, `password`, `email`) VALUES (?, ?, ?, ?)" 
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 19 users.active may not be NULL' in /home/u972751794/public_html/billing/classes/UserSystem.class.php:98 Stack trace: #0 /home/u972751794/public_html/billing/classes/UserSystem.class.php(98): PDOStatement->execute() #1 /home/u972751794/public_html/billing/classes/UserSystem.class.php(122): UserSystem->attachValues('insert into `us...', Array) #2 /home/u972751794/public_html/billing/login.php(69): UserSystem->addNewUser(Array) #3 {main} thrown in /home/u972751794/public_html/billing/classes/UserSystem.class.php on line 98

[requinix]

You're binding just fine.

 

SQLSTATE[23000]: Integrity constraint violation: 19 users.active may not be NULL

Since active isn't in your list of columns to insert, the database will use the default value. But the default is (apparently) null. The result is that you have to specify the active value when adding a new user.

[fear126]

i enter all columns names but still values are not getting bind in query :S 

string(154) "insert into `users` (`name`, `username`, `password`, `email`, `active`, `lastLogin`, `ip`, `resetToken`, `resetStatus`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"

[Jacques1]

What exactly is the problem? Actually, why don't you just fix your database and add the missing default values?

 

Also note that your function has no protection against SQL injection attacks through the array keys. A far more secure and robust approach is to explicitly write down the columns in the query. This not only prevents injection attacks. It also protects columns which should never be manually defined (like auto-incremented IDs).

[requinix]

What is the new error message?

[fear126]

What is the new error message?

 no error just that query is getting generated

 

Using input :

$registerFields = array(
		"Name" => "qwerty",
		"username" => "qwerty",
		"password" => "qwerty",
		"email" =>"qwerty@qwerty.com",
		"active" => '1',
		"lastLogin" => "",
		"ip" => "",
		"resetToken" => "",
		"resetStatus" => "0"
	);
$userSystem->addNewUser($registerFields);

[Jacques1]

What – is – the – problem? Or did you just want to tell us that it's fine now?

 

Also read #4.

[fear126]

What – is – the – problem? Or did you just want to tell us that it's fine now?

 

Also read #4.

problem is that the "?" in query are not getting replaced with the values i am supplying in array.

But in function attachValues $para

is having the arguments which are getting passed but they are not getting bind in query that is problem if you want i can send you whole file run and take a look on it

[requinix]

The question marks are supposed to be there. That's how prepared statements work.

 

Wikipedia

[fear126]

The question marks are supposed to be there. That's how prepared statements work.

 

Wikipedia

can i please email you full file so you can look over it ?

[Jacques1]

All you have to do is turn your brain back on and describe the problem in plain English. Something like: “I've tried to insert a new user with the values ..., but when I check the record in the database, all fields are empty”. Is it so hard?

 

But since you're hoplessly confused, and since the approach is generally poor, I suggest you scrap the code and start over. For example:

// TODO: distinguish between required and optional attributes
const USER_ATTRIBUTES = ['name', 'username', 'password', 'email', /* ... */];

public function addNewUser(array $data)
{
    $values = [];
    foreach (static::USER_ATTRIBUTES as $att)
    {
        if (!isset($data[$att]))
        {
            throw new InvalidArgumentException('Missing user attribute: '.$att);
        }

        $values[] = $data[$att];
    }

    $addQuery = '
      INSERT INTO
        users ('.implode(',', array_values(static::USER_ATTRIBUTES)).')
      VALUES
        ('.implode(',', array_fill(0, count(static::USER_ATTRIBUTES), '?')).')
    ';

    $this->attachValues($addQuery, $values);
}

private function attachValues($sqlQuery, array $params = [])
{
    if ($params)
    {
        $stmt = $this->database->prepare($sqlQuery);
        $stmt->execute($params);
    }
    else
    {
        $this->database->query($sqlQuery);
    }
}