>数组([0]=> 42000[1]=> 1064[2]=>,在SQL >语法中有一个错误;

时间:2022-04-24 14:32:03

I am using the following signup/login php script with mysql:

我正在使用以下的注册/登录php脚本和mysql:

https://github.com/panique/php-login-advanced>

https://github.com/panique/php-login-advanced >

The thing is that when I modify the Registration class found here: https://github.com/panique/php-login-advanced/blob/master/classes/Registration.php

当我修改在这里找到的注册类时,问题是:https://github.com/panique/php- loginadvanced/blob/master/classes/registration .php。

in order to add my extra fields I have crated in my table in the database the registration does not work. The error I get is:

为了将我的额外字段添加到数据库中的表中,注册就不起作用了。我得到的错误是:


Array ( [0] => 42000 [1] => 1064 [2] => 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 '' at line 7 )

数组([0]=> 42000[1]=> 1064[2]=>在SQL语法中有错误;检查与MySQL服务器版本对应的手册,以便在“第7行”附近使用。


My table is this one:

我的桌子是这个:

CREATE TABLE `users` (
`user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'auto incrementing user_id of each        user, unique index', 
`user_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'user''s name, unique',
`user_password_hash` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'user''s password in salted and hashed format',
`user_email` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'user''s email, unique',
`user_active` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'user''s activation status',
`user_activation_hash` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'user''s email verification hash string',
`user_password_reset_hash` char(40) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT  'user''s password reset code',
`user_password_reset_timestamp` bigint(20) DEFAULT NULL COMMENT 'timestamp of the password reset request',
`user_rememberme_token` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT  'user''s remember-me cookie token',
`user_failed_logins` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'user''s failed login attemps',
`user_last_failed_login` int(10) DEFAULT NULL COMMENT 'unix timestamp of last failed login attempt',
`user_registration_datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`user_registration_ip` varchar(39) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0.0.0.0',
`firstname` char(50) CHARACTER SET utf8 NOT NULL,
`lastname` char(100) CHARACTER SET utf8 NOT NULL,
`gender` enum('Woman','Man') COLLATE utf8_unicode_ci DEFAULT NULL,
`dob` date NOT NULL COMMENT 'date of birth',
`city` char(255) CHARACTER SET utf8 NOT NULL,
`address` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`zipcode` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`county` varchar(25) CHARACTER SET utf8 DEFAULT NULL,
`phone_number` int(14) NOT NULL,
`nationality` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`occupation` char(25) CHARACTER SET utf8 DEFAULT NULL,
`income` varchar(15) CHARACTER SET utf8 DEFAULT NULL,
`marital_status` char(15) CHARACTER SET utf8 DEFAULT NULL,
`account_permissions` enum('user','admin') CHARACTER SET utf8 NOT NULL DEFAULT 'user',
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_name` (`user_name`),
UNIQUE KEY `user_email` (`user_email`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci   COMMENT='user data';

` I modify the Registration class as following:

“我修改注册类别如下:

public function __construct()
{
    session_start();    
    // if we have such a POST request, call the registerNewUser() method
    if (isset($_POST["register"])) {
        $this->registerNewUser($_POST['user_name'],
                                $_POST['firstname'],
                                $_POST['lastname'],
                                $_POST['sexradio'],
                                $_POST['dob'],
                                $_POST['city'],
                                $_POST['address'],
                                $_POST['zipcode'],
                                $_POST['county'],
                                $_POST['phone'],
                                $_POST['user_email'],
                                $_POST['nationality'],
                                $_POST['position'],
                                $_POST['income'],
                                $_POST['maritalstatusradio'],
                                $_POST['user_password_new'], 
                                $_POST['user_password_repeat'], 
                                $_POST["captcha"]);
    // if we have such a GET request, call the verifyNewUser() method
    }

and inside the registerNewUser function I am adding:

在registerNewUser函数中,我添加了:

$query_new_user_insert = $this->db_connection->prepare('INSERT INTO users (user_name, user_password_hash, user_email, user_activation_hash, user_registration_ip, user_registration_datetime, firstname, lastname, gender, dob, city, address, zipcode, county, phone_number, nationality, occupation, income, marital_status) 
VALUES(:user_name, :user_password_hash, :user_email, :user_activation_hash, :user_registration_ip, now(), :firstname, :lastname, :sexradio, :dob, :city, :address, :zipcode, :county, :phone, :nationality, :position, :income, :maritalstatusradio');

and

$query_new_user_insert->bindValue(':user_name', $user_name, PDO::PARAM_STR);
$query_new_user_insert->bindValue(':user_password_hash', $user_password_hash, PDO::PARAM_STR);
$query_new_user_insert->bindValue(':user_email', $user_email, PDO::PARAM_STR);
$query_new_user_insert->bindValue(':user_activation_hash', $user_activation_hash, PDO::PARAM_STR);
$query_new_user_insert->bindValue(':user_registration_ip', $_SERVER['REMOTE_ADDR'], PDO::PARAM_STR);
$query_new_user_insert->bindValue(':firstname', $firstname, PDO::PARAM_STR);
$query_new_user_insert->bindValue(':lastname', $lastname, PDO::PARAM_STR);
$query_new_user_insert->bindValue(':sexradio', $sexradio, PDO::PARAM_STR);
$query_new_user_insert->bindValue(':dob', $dob, PDO::PARAM_STR);
$query_new_user_insert->bindValue(':city', $city, PDO::PARAM_STR);
$query_new_user_insert->bindValue(':address', $address, PDO::PARAM_STR);
$query_new_user_insert->bindValue(':zipcode', $zipcode, PDO::PARAM_STR);
$query_new_user_insert->bindValue(':county', $county, PDO::PARAM_STR);
$query_new_user_insert->bindValue(':phone', $phone, PDO::PARAM_STR);
$query_new_user_insert->bindValue(':nationality', $nationality, PDO::PARAM_STR);
$query_new_user_insert->bindValue(':position', $position, PDO::PARAM_STR);
$query_new_user_insert->bindValue(':income', $income, PDO::PARAM_STR);
$query_new_user_insert->bindValue(':maritalstatusradio', $maritalstatusradio, PDO::PARAM_STR);
$query_new_user_insert->execute();

the html form is:

html表单:

<form method="post" action="register.php" name="registerform" id="regForm">
  <fieldset>
   <label for="user_name">Kasutajanimi*</label>
   <input id="user_name" type="text" pattern="[a-zA-Z0-9]{2,64}" name="user_name" required />
  <?php echo WORDING_REGISTRATION_USERNAME; ?>
  </fieldset>
  <fieldset>
   <label>Eesnimi*</label>
   <input name="firstname" type="text">
  </fieldset>
  <fieldset>
   <label>Perkonnanimi*</label>
   <input name="lastname" type="text">
  </fieldset>
  <fieldset class="radio-group">
   <h3>Sugu</h3>
   <div class="radio-options">
   <p>
    <input name="sexradio" type="radio" id="sex-man"> 
    <label for="sex-man">Mees</label>
   </p>
   <p>
     <input name="sexradio" type="radio" id="sex-woman"> 
     <label for="sex-woman">Naine</label>
   </p>
   </div>
  </fieldset>
  <fieldset>
   <label>Sünniaeg*</label>
   <input name="dob" type="text">
  </fieldset>
  <fieldset>
    <label>Linn/asula*</label>
    <input name="city" type="text">
  </fieldset>
  <fieldset>
    <label>Aadress</label>
    <input name="address" type="text">
  </fieldset>
  <fieldset>
    <label>Postikood</label>
    <input name="zipcode" type="text">
  </fieldset>
  <fieldset>
   <label>Maakond</label>
   <select name="county" id="select-county">
       <option>Harju maakond</option>
       <option>Hiiu maakond</option>
       <option>Ida-Viru maakond</option>
   </select>
  </fieldset>
  <fieldset>
     <label>Sissetulek</label>
     <select name="income" id="select-income">
        <option>kuni 500</option>
        <option>500-1000</option>
        ...
     </select>
   </fieldset>
   <fieldset class="radio-group">
     <h3>Perekonnaseis</h3>
     <div class="radio-options">
     <p>
      <input name="maritalstatusradio" type="radio" id="marital-status-single"> 
      <label for="marital-status-single">Vallaline</label>
     </p>
     <p><input name="maritalstatusradio" type="radio" id="marital-status-married"> <label for="marital-status-married">Abielus</label></p>
     <p><input name="maritalstatusradio" type="radio" id="marital-status-cohabitation"> <label for="marital-status-cohabitation">Vabaabielu</label></p>
     <p><input name="maritalstatusradio" type="radio" id="marital-status-widow"> <label for="marital-status-widow">Lesk</label></p>
     </div>
    </fieldset>
    <fieldset>
     <label for="user_password_new">Password</label>
     <input id="user_password_new" type="password" name="user_password_new" pattern=".{6,}" required autocomplete="off" />
     <?php echo WORDING_REGISTRATION_PASSWORD; ?>
    </fieldset>
    <fieldset>
     <label for="user_password_repeat">Repeat password</label>
     <input id="user_password_repeat" type="password" name="user_password_repeat" pattern=".{6,}" required autocomplete="off" />
     <?php echo WORDING_REGISTRATION_PASSWORD_REPEAT; ?>
     </fieldset>
     <img src="tools/showCaptcha.php" alt="captcha" />
     <fieldset>
     <label><?php echo WORDING_REGISTRATION_CAPTCHA; ?></label>
     <input type="text" name="captcha" required />
     </fieldset>
     <fieldset>
      <input type="submit" name="register" value="Registreeru" class="btn"/>
     </fieldset>

    </form>

and the registerNewUser method

和registerNewUser方法

    private function registerNewUser($user_name, $firstname, $lastname, 
    $sexradio, $dob, $city, $address, $zipcode, $county, $phone, $user_email, 
    $nationality, $position, $income, $maritalstatusradio, $user_password,  $user_password_repeat, $captcha)

    {
    // we just remove extra space on username and email
    $user_name  = trim($user_name);
    $user_email = trim($user_email);

    // check provided data validity
    // TODO: check for "return true" case early, so put this first
    if (strtolower($captcha) != strtolower($_SESSION['captcha'])) {
        $this->errors[] = MESSAGE_CAPTCHA_WRONG;
    } elseif (empty($user_name)) {
        $this->errors[] = MESSAGE_USERNAME_EMPTY;

    } elseif (empty($firstname)) {
        $this->errors[] = MESSAGE_FIRSTNAME_EMPTY;
    } elseif (empty($lastname)) {
        $this->errors[] = MESSAGE_LASTNAME_EMPTY;
    } elseif (empty($dob)) {
        $this->errors[] = MESSAGE_DOB_EMPTY;
    } elseif (empty($city)) {
        $this->errors[] = MESSAGE_CITY_EMPTY;
    } elseif (empty($phone)) {
        $this->errors[] = MESSAGE_PHONE_EMPTY;


    } elseif (empty($user_password) || empty($user_password_repeat)) {
        $this->errors[] = MESSAGE_PASSWORD_EMPTY;
    } elseif ($user_password !== $user_password_repeat) {
        $this->errors[] = MESSAGE_PASSWORD_BAD_CONFIRM;
    } elseif (strlen($user_password) < 6) {
        $this->errors[] = MESSAGE_PASSWORD_TOO_SHORT;
    } elseif (strlen($user_name) > 64 || strlen($user_name) < 2) {
        $this->errors[] = MESSAGE_USERNAME_BAD_LENGTH;
    } elseif (!preg_match('/^[a-z\d]{2,64}$/i', $user_name)) {
        $this->errors[] = MESSAGE_USERNAME_INVALID;
    } elseif (empty($user_email)) {
        $this->errors[] = MESSAGE_EMAIL_EMPTY;
    } elseif (strlen($user_email) > 64) {
        $this->errors[] = MESSAGE_EMAIL_TOO_LONG;
    } elseif (!filter_var($user_email, FILTER_VALIDATE_EMAIL)) {
        $this->errors[] = MESSAGE_EMAIL_INVALID;

    // finally if all the above checks are ok
    } else if ($this->databaseConnection()) {
        // check if username or email already exists
        $query_check_user_name = $this->db_connection->prepare('SELECT user_name, user_email FROM users WHERE user_name=:user_name OR user_email=:user_email');
        $query_check_user_name->bindValue(':user_name', $user_name, PDO::PARAM_STR);
        $query_check_user_name->bindValue(':user_email', $user_email, PDO::PARAM_STR);
        $query_check_user_name->execute();
        $result = $query_check_user_name->fetchAll();

        // if username or/and email find in the database
        // TODO: this is really awful!
        if (count($result) > 0) {
            for ($i = 0; $i < count($result); $i++) {
                $this->errors[] = ($result[$i]['user_name'] == $user_name) ? MESSAGE_USERNAME_EXISTS : MESSAGE_EMAIL_ALREADY_EXISTS;
            }
        } else {
            // check if we have a constant HASH_COST_FACTOR defined (in config/hashing.php),
            // if so: put the value into $hash_cost_factor, if not, make $hash_cost_factor = null
            $hash_cost_factor = (defined('HASH_COST_FACTOR') ? HASH_COST_FACTOR : null);

            // crypt the user's password with the PHP 5.5's password_hash() function, results in a 60 character hash string
            // the PASSWORD_DEFAULT constant is defined by the PHP 5.5, or if you are using PHP 5.3/5.4, by the password hashing
            // compatibility library. the third parameter looks a little bit shitty, but that's how those PHP 5.5 functions
            // want the parameter: as an array with, currently only used with 'cost' => XX.
            $user_password_hash = password_hash($user_password, PASSWORD_DEFAULT, array('cost' => $hash_cost_factor));
            // generate random hash for email verification (40 char string)
            $user_activation_hash = sha1(uniqid(mt_rand(), true));

            // write new users data into database
            $query_new_user_insert = $this->db_connection->prepare('INSERT INTO users (user_name, 
                user_password_hash, user_email, user_activation_hash, user_registration_ip, user_registration_datetime, 
                firstname, lastname, gender, dob, city, address, zipcode, county, phone_number, nationality, 
                occupation, income, marital_status) 
            VALUES(:user_name, :user_password_hash, :user_email, :user_activation_hash, :user_registration_ip, now(), 
                :firstname, :lastname, :sexradio, :dob, :city, :address, :zipcode, :county, :phone, :nationality, 
                :position, :income, :maritalstatusradio');


            $query_new_user_insert->bindValue(':user_name', $user_name, PDO::PARAM_STR);
            $query_new_user_insert->bindValue(':user_password_hash', $user_password_hash, PDO::PARAM_STR);
            $query_new_user_insert->bindValue(':user_email', $user_email, PDO::PARAM_STR);
            $query_new_user_insert->bindValue(':user_activation_hash', $user_activation_hash, PDO::PARAM_STR);
            $query_new_user_insert->bindValue(':user_registration_ip', $_SERVER['REMOTE_ADDR'], PDO::PARAM_STR);

            $query_new_user_insert->bindValue(':firstname', $firstname, PDO::PARAM_STR);
            $query_new_user_insert->bindValue(':lastname', $lastname, PDO::PARAM_STR);
            $query_new_user_insert->bindValue(':sexradio', $sexradio, PDO::PARAM_STR);
            $query_new_user_insert->bindValue(':dob', $dob, PDO::PARAM_STR);
            $query_new_user_insert->bindValue(':city', $city, PDO::PARAM_STR);
            $query_new_user_insert->bindValue(':address', $address, PDO::PARAM_STR);
            $query_new_user_insert->bindValue(':zipcode', $zipcode, PDO::PARAM_STR);
            $query_new_user_insert->bindValue(':county', $county, PDO::PARAM_STR);
            $query_new_user_insert->bindValue(':phone', $phone, PDO::PARAM_STR);
            $query_new_user_insert->bindValue(':nationality', $nationality, PDO::PARAM_STR);
            $query_new_user_insert->bindValue(':position', $position, PDO::PARAM_STR);
            $query_new_user_insert->bindValue(':income', $income, PDO::PARAM_STR);
            $query_new_user_insert->bindValue(':maritalstatusradio', $maritalstatusradio, PDO::PARAM_STR);

            $query_new_user_insert->execute();

            $errorMessage=$query_new_user_insert->errorInfo(); 
            print_r($errorMessage);
            // id of new user
            $user_id = $this->db_connection->lastInsertId();

            if ($query_new_user_insert) {
                // send a verification email
                if ($this->sendVerificationEmail($user_id, $user_email, $user_activation_hash)) {
                    // when mail has been send successfully
                    $this->messages[] = MESSAGE_VERIFICATION_MAIL_SENT;
                    $this->registration_successful = true;
                } else {
                    // delete this users account immediately, as we could not send a verification email
                    $query_delete_user = $this->db_connection->prepare('DELETE FROM users WHERE user_id=:user_id');
                    $query_delete_user->bindValue(':user_id', $user_id, PDO::PARAM_INT);
                    $query_delete_user->execute();

                    $this->errors[] = MESSAGE_VERIFICATION_MAIL_ERROR;
                }
            } else {
                $this->errors[] = MESSAGE_REGISTRATION_FAILED;
            }
        }
    }
}

The problem is somewhere in the insert statement and probably has to do with the quotation marks.

问题是在insert语句中,可能与引号有关。

Any help appreciated.! :)

任何帮助表示赞赏。:)

1 个解决方案

#1


0  

Finally the error was because of a parenthesis that I hadn't closed on the prepare function which holds the sql statement:

最后,错误是由于我没有关闭准备函数的括号,它持有sql语句:

$query_new_user_insert = $this->db_connection->prepare('INSERT INTO users (user_name,   user_password_hash, user_email, user_activation_hash, user_registration_ip, user_registration_datetime, firstname, lastname, gender, dob, city, address, zipcode, county, phone_number, nationality, occupation, income, marital_status) 
VALUES(:user_name, :user_password_hash, :user_email, :user_activation_hash, :user_registration_ip, now(), :firstname, :lastname, :sexradio, :dob, :city, :address, :zipcode, :county, :phone, :nationality, :position, :income, :maritalstatusradio') );

#1


0  

Finally the error was because of a parenthesis that I hadn't closed on the prepare function which holds the sql statement:

最后,错误是由于我没有关闭准备函数的括号,它持有sql语句:

$query_new_user_insert = $this->db_connection->prepare('INSERT INTO users (user_name,   user_password_hash, user_email, user_activation_hash, user_registration_ip, user_registration_datetime, firstname, lastname, gender, dob, city, address, zipcode, county, phone_number, nationality, occupation, income, marital_status) 
VALUES(:user_name, :user_password_hash, :user_email, :user_activation_hash, :user_registration_ip, now(), :firstname, :lastname, :sexradio, :dob, :city, :address, :zipcode, :county, :phone, :nationality, :position, :income, :maritalstatusradio') );