Database insertion error

Question

The below echo statement,

$statement = "INSERT INTO $tbl_name VALUES(" . $_GET['username'] . "," . $_GET['password'] . "," . $_GET['PasswordHintQuestion'] . "," . $_GET['PasswordHintAnswer'] . "," . $_GET['firstname'] . "," . $_GET['lastname'] . "," . $_GET['genderSelect'] . "," . $_GET['date_in_format'] . "," . $_GET['nationality'] . "," . $_GET['refEmail'] . ")" ;   
echo $statement;

gave the ouput as,

INSERT INTO ge_user_table VALUES([object HTMLInputElement],[object HTMLInputElement],[object HTMLInputElement],[object HTMLInputElement],[object HTMLInputElement],[object HTMLInputElement],[object NodeList],[object HTMLSelectElement]/[object HTMLSelectElement]/[object HTMLSelectElement],[object HTMLInputElement],[object HTMLInputElement])Database Insertion fault on registration

But during insertion into database I got the error as,

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 '[object HTMLInputElement],[object HTMLInputElement],[object HTMLInputElement],[o' at line 1

But, the below query is working fine.

INSERT INTO ge_user_table VALUES('Muthu2','1234','Who are you?','Iam Indian','Muthu','Ganapathy','MALE','1991-12-21','Indian','abc@abc.com');

EDIT : I have changed the code to,

  $username     = mysql_escape_string($_GET['username']);
  $password     = mysql_escape_string($_GET['password']);
  $hintQues     = mysql_escape_string($_GET['PasswordHintQuestion']);
  $hintAns      = mysql_escape_string($_GET['PasswordHintAnswer']);
  $firstname    = mysql_escape_string($_GET['firstname']);
  $hintQues     = mysql_escape_string($_GET['lastname']);
  $gender       = mysql_escape_string($_GET['genderSelect']);
  $date         = mysql_escape_string($_GET['date_in_format']) ;
  $nationality  = mysql_escape_string($_GET['nationality']) ;
  $email        = mysql_escape_string($_GET['refEmail']) ;



    $statement = "INSERT INTO $tbl_name VALUES('$username' ,'$password','$hintQues' ,'$hintAns','$firstname' ,'$lastname' ,".
               "'$gender' ,'$date','$nationality','$email')" ;

But,the database has entry as, enter image description here

Final Solution: I have passed form.username in html instead of form.username.value. Now Got it correct.

you need to learn basic SQL syntax. String literal formatting in particular. strings in SQL must be quoted **and escaped.** — Your Common Sense, Mar 13 '13 at 09:14
[The Great Escapism (Or: What You Need To Know To Work With Text Within Text)](http://kunststube.net/escapism/) — deceze, Mar 13 '13 at 09:15

score 2 · Answer 1 · answered Mar 13 '13 at 09:16

2

It look like you have error in javascript. you send html DOM Node instead of value.

Also you should escape your get variables like

mysql_real_escape_string($_GET['username']);

answered Mar 13 '13 at 09:16

jcubic

61,973
54
229
402

+1 as this is another error, in addition to SQL (mis)formatting – Your Common Sense Mar 13 '13 at 09:17
not whatever "get variables" but "SQL strings". – Your Common Sense Mar 13 '13 at 09:34
@Your Common Sense see the edit. – Muthu Ganapathy Nathan Mar 13 '13 at 11:23

alwaysLearn · Answer 2 · 2013-03-13T11:45:34.000

1

TRY THIS

  $username     = mysql_escape_string($_GET['username']);
  $password     = mysql_escape_string($_GET['password']);
  $hintQues     = mysql_escape_string($_GET['PasswordHintQuestion']);
  $hintAns      = mysql_escape_string($_GET['PasswordHintAnswer']);
  $firstname    = mysql_escape_string($_GET['firstname']);
  $hintQues     = mysql_escape_string($_GET['lastname']);
  $gender       = mysql_escape_string($_GET['genderSelect']);
  $date         = mysql_escape_string($_GET['date_in_format']) ;
  $nationality  = mysql_escape_string($_GET['nationality']) ;
  $email        = mysql_escape_string($_GET['refEmail']) ;



  $statement = "INSERT INTO $tbl_name VALUES('$username' ,'$password','$hintQues' ,'$hintAns','$firstname' ,'$lastname' ,".
               "'$gender' ,'$date','$nationality','$email')" ;   

  echo $statement;

Always try to keep the statement as readable as possible .. also whenever string needs to be inserted .. it should be propery quoted Also always use mysql_escape_string() to avoid sql injection.

Possible problem can be ..you are passing html element itself instead of its value

edited Mar 13 '13 at 11:45

answered Mar 13 '13 at 09:30

alwaysLearn

6,882
7
39
67

Why only 2 fields formatted? – Your Common Sense Mar 13 '13 at 09:33
@YourCommonSense Oh sorry my mistake ... I am just editing it – alwaysLearn Mar 13 '13 at 09:49
@EAGER_STUDENT ok post me the html code for the element which your are accessing as $_GET['username'] – alwaysLearn Mar 13 '13 at 11:25
@new_developer k. it is like, the mistake is on another file. rectifying it. thank you. – Muthu Ganapathy Nathan Mar 13 '13 at 11:27
@new_developer The problem is with me. I have passed form.username instead of form.username.value. sorry, Thank_you. – Muthu Ganapathy Nathan Mar 13 '13 at 11:32
@EAGER_STUDENT Ok it happens often.. no problem – alwaysLearn Mar 13 '13 at 11:33
@EAGER_STUDENT I think you should edit your question back – alwaysLearn Mar 13 '13 at 11:34

score 0 · Answer 3 · answered Mar 13 '13 at 09:26

Your sql syntax is wrong you can use mysql_real_escape_string but you also need to care about how you are passing values to sql.

In above query you symply passed text without quotes.

$statement = "INSERT INTO $tbl_name VALUES('".$_GET['username']."', '".$_GET['password']."', '".$_GET['PasswordHintQuestion']."', '".$_GET['PasswordHintAnswer']."', '".$_GET['firstname']."', '".$_GET['lastname']."', '".$_GET['genderSelect']."', '".$_GET['date_in_format']."', '".$_GET['nationality']."', '".$_GET['refEmail']."')" ;

Database insertion error

3 Answers3