SQL Insert Issue with PHP

Question

I have the following code to insert data into a DB but when inserting the data some times it insert successfully while most of the time its giving me error like below. Could not enter data: 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 's fist name?', 'Kabul Janm', 'Afghanistan', 'Kabul', 'Kabul', '1985-03-26', 'Mal' at line 1

Can someone help me out, I need to have it stable, the code is as below please,

 <html>
 <head>
<title>Add New Record in MySQL Database</title>
<script src="SpryAssets/SpryCollapsiblePanel.js" type="text/javascript"></script>
<script src="SpryAssets/SpryTabbedPanels.js" type="text/javascript"></script>
<link href="SpryAssets/SpryCollapsiblePanel.css" rel="stylesheet" type="text/css" />
</head>
 <body>
<?php
 include_once ('top.php');
 ?>
 <?php
 include '/Connections/conn.php';
  if(isset($_POST['add']))
   {
  if(! $conn )
  {
  die('Could not connect: ' . mysql_error());
    }

   if(! get_magic_quotes_gpc() )
   {
    $first_name = addslashes ($_POST['first_name']);
    $last_name = addslashes ($_POST['last_name']);
      }
   else
   {
    $first_name = $_POST['first_name'];
     $last_name = $_POST['last_name'];
    }
     $email_address = $_POST['email_address'];
     $phone_no = $_POST['Phone_no'];
     $user_name = $_POST['user_name'];
       $password = $_POST['password'];
       $sec_question = $_POST['sec_question'];
       $Answer = $_POST['Answer'];
       $Country = $_POST['Country'];
       $State = $_POST['State'];
        $city = $_POST['city'];
        $date_birth = $_POST['date_birth'];
       $gender = $_POST['gender'];

         $sql = "INSERT INTO users(first_name, last_name, email_address, Phone_no,        user_name, password, sec_question, Answer, Country, State, city, date_birth, gender) VALUES('$first_name', '$last_name', '$email_address',  '$phone_no', '$user_name', '$password',  '$sec_question', '$Answer', '$Country', '$State', '$city', '$date_birth', '$gender')";
     $dbname;
      $retval = mysql_query( $sql, $conn );
  if(! $retval )
  {
   die('Could not enter data: ' . mysql_error());
   }
   header("Location: /thank.php");
    //echo "<center>Thanks for registration in Mashwani Info Tech Free Online Trainings (MOFT)</center>\n";
     mysql_close($conn);
      }
     else
     {
     ?>
       <script src="SpryAssets/SpryValidationTextField.js" type="text/javascript"></script>
       <link href="SpryAssets/SpryValidationTextField.css" rel="stylesheet" type="text/css" />
       <link href="SpryAssets/SpryValidationPassword.css" rel="stylesheet" type="text/css" />
        <link href="SpryAssets/SpryValidationConfirm.css" rel="stylesheet" type="text/css" />
        <link href="SpryAssets/SpryValidationSelect.css" rel="stylesheet" type="text/css" />
         <script src="SpryAssets/SpryValidationPassword.js" type="text/javascript"></script>
           <script src="SpryAssets/SpryValidationConfirm.js" type="text/javascript"></script>
             <script src="SpryAssets/SpryValidationSelect.js" type="text/javascript"></script>

          <table width="100%" background="/Images/gradient_medium.jpg"> 

         <tr>
           <td width="100%" height="34"> 
            <!--<center> <marquee behavior="Scroll" width="100%" scrollamount="8" direction="Right"><img src="/Images/mtn.jpg" /> <img src="/Images/mtn1.jpg" /></marquee>  </center>
            -->
           </td>
           </tr>
           </table>
            <table width="100%" align="center" bgcolor="#ECF5F0" border="0">
           <tr valign="bottom">    <td height="25"><p>&nbsp;</p>
        <form action="<?php $_PHP_SELF?>" method="post" name="form1" id="form1">
        <table align="center" border="1">
          <tr valign="baseline">
          <td colspan="2" align="left" nowrap="nowrap" bordercolor="#CCCC33">First Name</td>
            <td width="388"><span id="sprytextfield1">
             <input type="text" name="first_name" value="" size="37" id="first_name"/>
             <span class="textfieldRequiredMsg">A value is required.</span></span></td>
             </tr>
             <tr valign="baseline">
                 <td colspan="2" align="left" nowrap="nowrap">Last Name</td>
               <td><input type="text" name="last_name" id="last_name" value="" size="37" /></td>   
              </tr>
              <tr valign="baseline">
                <td colspan="2" align="left" nowrap="nowrap">Email Address</td>
               <td><span id="sprytextfield2">
               <input type="text" name="email_address" id="email_address" value="" size="37" />
                <span class="textfieldRequiredMsg">A value is required.</span><span class="textfieldInvalidFormatMsg">Invalid format.</span></span></td>
      </tr>
              <tr valign="baseline">
              <td colspan="2" align="left" nowrap="nowrap">Phone No <font size="-4" color="#00CC66">(0093772221521)</font></td>
             <td><span id="sprytextfield3">
             <input type="text" name="Phone_no" id="Phone_no" value="" size="37" />
             <span class="textfieldRequiredMsg">A value is required.</span><span class="textfieldInvalidFormatMsg">Invalid format.</span></span></td>
             </tr>
             <tr valign="baseline">
             <td colspan="2" align="left" nowrap="nowrap">User Name</td>
             <td><span id="sprytextfield4">
              <input type="text" name="user_name" id="user_name" value="" size="37" />
              <span class="textfieldRequiredMsg">A value is required.</span></span></td>
               </tr>
              <tr valign="baseline">
             <td colspan="2" align="left" nowrap="nowrap">Password <font size="-4" color="#00CC66">(Min 8 Charectors) </font></td>
            <td><span id="pass">
           <input type="password" name="password" value="" size="37" id="password" />
        <span class="passwordRequiredMsg">A value is required.</span><span class="passwordInvalidStrengthMsg">The password doesn't meet the specified strength.</span></span></td>
      </tr>
        <tr valign="baseline">
        <td colspan="2" align="left" nowrap="nowrap">Confirm Password:</td>
        <td><span id="spryconfirm1">
          <label for="confirm"></label>
          <input name="confirm" type="password" id="confirm" size="37" />
          <span class="confirmRequiredMsg">A value is required.</span><span class="confirmInvalidMsg">The values don't match.</span></span></td>
      </tr>

      <tr valign="baseline">
        <td colspan="2" align="left" nowrap="nowrap">Secret Question</td>
        <td><span id="spryselect1">
          <label for="sec"></label>
          <select name="sec_question" id="sec_question">
            <option value="What is your fist school name?">What is your fist school name?</option>
            <option value="Where did your birth happened?">Where did your birth happened?</option>
            <option value="What is your father's fist name?">What is your father's fist name?</option>
            <option value="Where did you get your degree?">Where did you get your degree?</option>
          </select>
        <span class="selectRequiredMsg">Please select an item.</span></span></td>
      </tr>
      <tr valign="baseline">
        <td colspan="2" align="left" nowrap="nowrap">Answer for Question</td>
        <td><span id="sprytextfield5">
          <input type="text" name="Answer" id="Answer" value="" size="37" />
        <span class="textfieldRequiredMsg">A value is required.</span></span></td>
      </tr>
      <tr valign="baseline">
        <td width="61" rowspan="3" align="left" valign="middle" nowrap="nowrap">Address</td>
        <td width="121" align="left" nowrap="nowrap">Country</td>
        <td><input type="text" name="Country" id="Country" value="" size="37" /></td>
      </tr>
      <tr valign="baseline">
        <td width="121" align="left" nowrap="nowrap">State</td>
        <td><span id="sprytextfield6">
          <input type="text" name="State" id="State" value="" size="37" />
        <span class="textfieldRequiredMsg">A value is required.</span></span></td>
      </tr>
      <tr valign="baseline">
        <td width="121" align="left" nowrap="nowrap">City</td>
        <td><span id="sprytextfield7">
          <input type="text" name="city" id="city" value="" size="37" />
        <span class="textfieldRequiredMsg">A value is required.</span></span></td>
      </tr>
      <tr valign="baseline">
        <td colspan="2" align="left" nowrap="nowrap">Date of Birth <font size="-4" color="#00CC66"> (YYYY-MM-DD) </font></td>
        <td><span id="sprytextfield8">
        <input type="text" name="date_birth" id="date_birth" value="" size="37" />
        <span class="textfieldRequiredMsg">A value is required.</span><span class="textfieldInvalidFormatMsg">Invalid format.</span></span></td>
      </tr>
      <tr valign="baseline">
        <td colspan="2" align="left" nowrap="nowrap">Gender</td>
        <td><span id="spryselect2">
          <label for="gen"></label>
          <select name="gender" id="gender">
            <option value="Select your gender here." selected="selected">Select your gender here.</option>
            <option value="Male">Male</option>
            <option value="Female">Female</option>
          </select>
        <span class="selectRequiredMsg">Please select an item.</span></span></td>
      </tr>
    </table>
    <center> <input name="add" type="submit" value="Sign Up" id="add" /> </center>
    <input type="hidden" name="ID" value="" />
    <input type="hidden" name="admin_level" id="admin_level" value="" />
    <input type="hidden" name="time_stamp" id="time_stamp" value="" />
    <input type="hidden" name="MM_insert" value="form1" />
  </form>
  <p>&nbsp;</p></td>
    </tr>

    </table>
    <?php
    }
   ?>    
    <script type="text/javascript">
   var sprytextfield1 = new Spry.Widget.ValidationTextField("sprytextfield1", "none", {validateOn:["blur"]});
    var sprytextfield2 = new Spry.Widget.ValidationTextField("sprytextfield2", "email");
    var sprytextfield3 = new Spry.Widget.ValidationTextField("sprytextfield3", "phone_number", {format:"phone_custom"});
    var sprytextfield4 = new Spry.Widget.ValidationTextField("sprytextfield4");
    var sprypassword1 = new Spry.Widget.ValidationPassword("pass", {minAlphaChars:1, minUpperAlphaChars:1, minSpecialChars:1, validateOn:["blur"]});
    var spryconfirm1 = new Spry.Widget.ValidationConfirm("spryconfirm1", "password", {validateOn:["blur"]});
      var spryselect1 = new Spry.Widget.ValidationSelect("spryselect1");
      var sprytextfield5 = new Spry.Widget.ValidationTextField("sprytextfield5");
      var sprytextfield6 = new Spry.Widget.ValidationTextField("sprytextfield6");
       var sprytextfield7 = new Spry.Widget.ValidationTextField("sprytextfield7");
        var sprytextfield8 = new Spry.Widget.ValidationTextField("sprytextfield8", "date", {format:"yyyy-mm-dd"});
        var spryselect2 = new Spry.Widget.ValidationSelect("spryselect2");
        var CollapsiblePanel1 = new Spry.Widget.CollapsiblePanel("CollapsiblePanel1");
         </script>
        <?php
        include_once ('bottom.php');
         ?>
        </body>
         </html>

You're not escaping your user input properly so any user-entered field that contains an apostrophe will cause a syntax error in your query. This is the root cause of SQL injection vulnerabilities. At the very least you should escape all your user input with `mysql_real_escape_string()`, but you should consider using prepared statements. Note also that `mysql_*()` is deprecated and you shouldn't use it for new code. Use `mysqli_*()` or `PDO` — , Apr 13 '14 at 09:10
hello thanks for reply but how and where to put the code escape to avoid this issue please, — Nooruddin Saghar, Apr 13 '14 at 13:30

score 0 · Accepted Answer · edited May 23 '17 at 11:49

You have to escape "'" signs before using data in a query.

What happens is that your users enter "'" in the input field, and MySQL gets an error.

Use prepared statements for automatical way to deal with that problem.

Here is and example.

Edit:

Edit your code like this:

if(! get_magic_quotes_gpc() )
{
    $first_name = addslashes ($_POST['first_name']);
    $last_name = addslashes ($_POST['last_name']);
    $email_address = addslashes ($_POST['email_address']);
    $phone_no = addslashes ($_POST['Phone_no']);
    $user_name = addslashes ($_POST['user_name']);
    $password = addslashes ($_POST['password']);
    $sec_question = addslashes ($_POST['sec_question']);
    $Answer = addslashes ($_POST['Answer']);
    $Country = addslashes ($_POST['Country']);
    $State = addslashes ($_POST['State']);
    $city = addslashes ($_POST['city']);
    $date_birth = addslashes ($_POST['date_birth']);
    $gender = addslashes ($_POST['gender']);
}
else
{
    $first_name = $_POST['first_name'];
    $last_name = $_POST['last_name'];
    $email_address = $_POST['email_address'];
    $phone_no = $_POST['Phone_no'];
    $user_name = $_POST['user_name'];
    $password = $_POST['password'];
    $sec_question = $_POST['sec_question'];
    $Answer = $_POST['Answer'];
    $Country = $_POST['Country'];
    $State = $_POST['State'];
    $city = $_POST['city'];
    $date_birth = $_POST['date_birth'];
    $gender = $_POST['gender'];
}

It should prevent the errors.

hello thanks for reply but how and where to put the code escape to avoid this issue please, — Nooruddin Saghar, Apr 13 '14 at 13:30
Dear. Am still waiting for the answer as I am fully new in Mysql and PHP thats why have now idea on short and less answer please to consider my query as critical. — Nooruddin Saghar, Apr 13 '14 at 16:07
I've given you a quick fix. Also, if you want to learn php, you might want to consider downloading some e-book with examples and following it through. — Anatoliy Kim, Apr 13 '14 at 16:17

SQL Insert Issue with PHP

1 Answers1