Unable to insert data from post array in MySQL

Question

As in topic, I'm beginner in programming but I've recently created some code that looks ok for me for now(yeah some things are messed up but I' going to work on it), so I have ordinary registration form looking like this:

    include 'modules/translator.php';
include 'includes/database.php';
include 'includes/header.php';

echo '
         
         '. $LANG['REGISTER'] .'
         '. $LANG['SIGNIN'] .'
         
         
         
         
         
         
         
         
         
         
         ';

include 'includes/footer.php';

And this is file which checks for errors(if format of e-mail is proper for example), and if no errors should insert data into database:

/*Include translating module,database settings,
functions needed to prepare data from form, 
to insert them into database and user interface header.*/

include 'modules/translator.php';
include 'includes/database.php';
include 'includes/functions.php';
include 'includes/header.php';

/*Variables including data from registration.php form.*/

$name=$_POST[regname];
$surname=$_POST[regsurname];
$nickname=$_POST[regnickname];
$email=$_POST[regemail];
$password=$_POST[regpass];
$rpassword=$_POST[regrpass];

/*Variable used to count errors occured during registration process.*/

$errorcounter=0;

/*Variables used to display errors that occured during registration process.*/

$errornameset='';
$errorname='';
$errornamelength='';
$errorsurnameset='';
$errorsurname='';
$errorsurnamelength='';
$errornicknameset='';
$errornickname='';
$errornicknamelength='';
$erroremailset='';
$erroremail='';
$errorpasswordset='';
$errorpassword='';
$errorpasswordlength='';
$errorpasswordcomparison='';
$registrationset1='';
$registrationset2='';
$errordbconnection='';


if(empty($name)){

     $errorcounter++;
     $errornameset='•' . $LANG['ERR_REG_NAME_SET'] . '
';

}
elseif(!preg_match('/^[A-ZĄĆĘŁŃÓŚŹŻ][a-ząćęłńóśźż]{2,}$/', $name)){

     $errorcounter++;
     $errorname='•' . $LANG['ERR_REG_NAME_CHAR'] . '
';

}
elseif(strlen($name)<3 || strlen($name)>16){

     $errorcounter++;
     $errornamelength='•' . $LANG['ERR_REG_NAME_LENGTH'] . '
';

}

if(empty($surname)){

     $errorcounter++;
     $errorsurnameset='•' . $LANG['ERR_REG_SURNAME_SET'] . '
';

}
elseif(!preg_match('/^[A-ZĄĆĘŁŃÓŚŹŻ][a-ząćęłńóśźż]{2,}$/', $surname)){

     $errorcounter++;
     $errorsurname='•' . $LANG['ERR_REG_SURNAME_CHAR'] . '
';

}
elseif(strlen($surname)<3 || strlen($surname)>32){

     $errorcounter++;
     $errorsurnamelength='•' . $LANG['ERR_REG_SURNAME_LENGTH'] . '
';

}

if(empty($nickname)){

     $errorcounter++;
     $errornicknameset='•' . $LANG['ERR_REG_NICKNAME_SET'] . '
';

}
elseif(!preg_match('/^[A-Za-z0-9]{6,}$/', $nickname)){

     $errorcounter++;
     $errornickname='•' . $LANG['ERR_REG_NICKNAME_CHAR'] . '
';

}
elseif(strlen($nickname)<3 || strlen($nickname)>12){

     $errorcounter++;
     $errornicknamelength='•' . $LANG['ERR_REG_NICKNAME_LENGTH'] . '
';

}

if(empty($email)){

     $errorcounter++;
     $erroremailset='•' . $LANG['ERR_REG_EMAIL_SET'] . '
';

     }
elseif(!preg_match('/^[A-Za-z0-9\.\-\_]{2,}\@[A-Za-z0-9\.\-\_]+\.[a-z]{2,4}$/', $email)){

     $errorcounter++;
     $erroremail='•' . $LANG['ERR_REG_EMAIL_CHAR'] . '
';

}

if(empty($password) || empty($rpassword)){

     $errorcounter++;
     $errorpasswordset='•' . $LANG['ERR_REG_PASSWORD_SET'] . '
';

     }
elseif(!preg_match('/^[A-Za-z0-9\.\-_@#]{6,}$/', $password)){

     $errorcounter++;
     $errorpassword='•' . $LANG['ERR_REG_PASSWORD_CHAR'] . '
';

}
elseif(strlen($password)<6 || strlen($password)>32){

     $errorcounter++;
     $errorpasswordlength='•' . $LANG['ERR_REG_PASSWORD_LENGTH'] . '
';

     }
elseif($password !== $rpassword){

     $errorcounter++;
     $errorpasswordcomparison='•' . $LANG['ERR_REG_PASSWORD_COMPARISON'] . '
';

}

if($errorcounter==1){

     echo '
'. $LANG['ERR_REG_COUNT_MESSAGE'] . $errorcounter . $LANG['ERR_REG_COUNT_1'] . '
' .
             $errornameset . 
             $errorname . 
             $errornamelength . 
             $errorsurnameset . 
             $errorsurname . 
             $errorsurnamelength . 
             $errornicknameset . 
             $errornickname . 
             $errornicknamelength . 
             $erroremailset . 
             $erroremail . 
             $errorpasswordset . 
             $errorpassword . 
             $errorpasswordlength . 
             $errorpasswordcomparison . 
             '
' . $LANG['REG_FIX'] . '';

}
elseif($errorcounter>1 && $errorcounter<5){

     echo '
'. $LANG['ERR_REG_COUNT_MESSAGE'] . $errorcounter . $LANG['ERR_REG_COUNT_1_5'] . '
' .
             $errornameset . 
             $errorname . 
             $errornamelength . 
             $errorsurnameset . 
             $errorsurname . 
             $errorsurnamelength . 
             $errornicknameset . 
             $errornickname . 
             $errornicknamelength . 
             $erroremailset . 
             $erroremail . 
             $errorpasswordset . 
             $errorpassword . 
             $errorpasswordlength . 
             $errorpasswordcomparison . 
             '
' . $LANG['REG_FIX'] . '';

}
elseif($errorcounter>4){

     echo '
'. $LANG['ERR_REG_COUNT_MESSAGE'] . $errorcounter . $LANG['ERR_REG_COUNT_4'] . '
' .
             $errornameset . 
             $errorname . 
             $errornamelength . 
             $errorsurnameset . 
             $errorsurname . 
             $errorsurnamelength . 
             $errornicknameset . 
             $errornickname . 
             $errornicknamelength . 
             $erroremailset . 
             $erroremail . 
             $errorpasswordset . 
             $errorpassword . 
             $errorpasswordlength . 
             $errorpasswordcomparison . 
             '
' . $LANG['REG_FIX'] . '';

}
else{

     $group=1;
     $token=md5(uniqid(rand(), true));
     $connection=mysqli_connect(db_server, db_user, db_password, db_name);
     $regquery="INSERT INTO users (name, surname, nickname, e-mail, password, group, token) VALUES ('".$name."', '".$surname."', '".$nickname."', '".$email."', '".$password."', '".$group."', '".$token."')";

     if(mysqli_query($connection, $regquery)){

         echo '
'. $LANG['REG_COMPLETED_HEADER'] .'
' .
             $registrationset1=$LANG['REG_COMPLETED_MESSAGE_1'] . $email . "." . $registrationset2=$LANG['REG_COMPLETED_MESSAGE_2'] .
             '
' . $LANG['REG_MAIN'] . '';

         mysqli_close($connection);

     }
     else{

         echo '
'. $LANG['DB_ERR_HEADER'] . '
' .
             $errordbconnection = $LANG['DB_ERR_CONNECTION'] .
             '
' . $LANG['REG_FIX'] . '';

     }

}

include 'includes/footer.php';

And that's it.As addition I can say that i checked if connection in this file works properly and test was positive.One more thing I guess, in div with warning class is everything negative and in div with alert class is everything positive.My script prints negative div which means that no records inserted into DB(I checked that in phpmyadmin-no records).I tried everything including rebuilding my code but this doesn't works.MySQL Improved is available in my hosting services.Any ideas dear programmers?Thank You for Your time and waiting in hope for good answers. :)

stj · Accepted Answer

The query string you're using

INSERT INTO users (name, surname, nickname, e-mail, password, group, token) VALUES ...

won't work in MySQL as it is. The reason is that e-mail (without quotes) is not a valid column name and that group is a keyword. So the query won't parse. Both column names need to be enclosed in backticks so MySQL will actually treat them as column names:

INSERT INTO users (name, surname, nickname, `e-mail`, password, `group`, token) VALUES ...

You should definitely turn on error reporting because this would have probably revealed this error (plus several more in the script) already. Additionally, as someone already pointed out, your script is definitely vulnerable to SQL injection and you should care about it.

Unable to insert data from post array in MySQL

Answers (1)

Related Questions