Click here to Skip to main content
12,074,808 members (57,836 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: PHP
When I do an insert the magic quotes work, but when I try to update a row it gives error:

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 'Road 12, St Julian\'s, UK'', lat = '35.911179', lng = '14.487689', ca' at line 1

Any ideas please?


<?php
 
function check_input($value){
    // Stripslashes
    if (get_magic_quotes_gpc())
      {
      $value = stripslashes($value);
      }
    // Quote if not a number
    if (!is_numeric($value))
      {
      $value = "'" . mysql_real_escape_string($value) . "'";
      }
    return $value;
}
 
//Get database credentials
require 'config.php';
 
$link = mysql_connect($dbhost, $dbusername,$dbpass);
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
    mysql_select_db($dbname) or die('Cannot select database');
 
    $id = $_GET['id'];
    $riskCategory = $_GET['riskCategory'];
    $EventAccidentSubject = $_GET['EventAccidentSubject'];
    $description = $_GET['description'];
    $peopleInvolved = $_GET['peopleInvolved'];
    $hazards = $_GET['hazards'];
    //$address = $_GET['address'];
    $address = check_input($_GET['address']);
    $lat = $_GET['lat'];
    $lng = $_GET['lng'];
    $caseStatus = $_GET['caseStatus'];
 
    $query = "UPDATE dataentry" .
        " SET riskCategory = '".$riskCategory."'," .
        " EventAccidentSubject = '".$EventAccidentSubject."'," .
        " description = '".$description."'," .
        " peopleInvolved = '".$peopleInvolved."'," .
        " hazards = '".$hazards."'," .
        " address = '".$address."',".
        " lat = '".$lat."'," .
        " lng = '".$lng."'," .
        " caseStatus = '".$caseStatus."'" .
" WHERE id = '".$id."'";
 
//Run the query
$result = mysql_query($query) or die(mysql_error());
 
//link variable is equal to the referring page
$link = $_SERVER['HTTP_REFERER'];
//sends a header directly to the browser telling it to redirect the user to the referring page
header("Location: $link");
?>
Posted 16-Apr-13 21:57pm
bmw318mt1.5K
Edited 18-Apr-13 6:28am
v2
Comments
Prasad Khandekar 18-Apr-13 11:33am
   
Please use bind parameters (parameterised Queries), the current method you are using is a perfect way to disaster. See this link for more info (http://php.net/manual/en/mysqli-stmt.bind-param.php) or this (http://php.net/manual/en/security.database.sql-injection.php)

Regards,
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Hello,

Try changing your update code to something similar to one shown below.
$mysqli = new mysqli("server", "username", "password", "database_name");
$query = "UPDATE dataentry SET riskCategory = ?, "  .
        "EventAccidentSubject = ? , " .
        "description = ? , " .
        "peopleInvolved = ? , " .
        "hazards = ?, " .
        "address = ?, ".
        "lat = ?, " .
        "lng = ?, " .
        "caseStatus = ? " .
        "WHERE id = ?";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("s", $riskCategory);
$stmt->bind_param("s", $EventAccidentSubject);
$stmt->bind_param("s", $description);
$stmt->bind_param("s", $peopleInvolved);
$stmt->bind_param("s", $hazards);
$stmt->bind_param("s", $address);
$stmt->bind_param("i", $lat);
$stmt->bind_param("i", $lng);
$stmt->bind_param("s", $caseStatus);
$stmt->bind_param("s", $id);
$stmt->execute();
$stmt->close();
$mysqli->close();
Regards,
  Permalink  
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

I have implemented this by I am getting an error, I can't figure out whats wrong
<?php
 
//Get database credentials
require 'config.php';
 

 
    $mysqli = new mysqli($dbhost,  $dbusername, $dbpass, $dbname);
 

 
$query = "UPDATE dataentry SET riskCategory = ?, "  .
        "EventAccidentSubject = ? , " .
        "description = ? , " .
        "peopleInvolved = ? , " .
        "hazards = ?, " .
        "address = ?, ".
        "lat = ?, " .
        "lng = ?, " .
        "caseStatus = ? " .
        "WHERE id = ?";
$stmt = $mysqli->prepare(query);
$stmt->bind_param("s", $riskCategory);
$stmt->bind_param("s", $EventAccidentSubject);
$stmt->bind_param("s", $description);
$stmt->bind_param("s", $peopleInvolved);
$stmt->bind_param("s", $hazards);
$stmt->bind_param("s", $address);
$stmt->bind_param("i", $lat);
$stmt->bind_param("i", $lng);
$stmt->bind_param("s", $caseStatus);
$stmt->bind_param("s", $id);
$stmt->execute();
$stmt->close();
$mysqli->close();
 
//link variable is equal to the referring page
$link = $_SERVER['HTTP_REFERER'];
//sends a header directly to the browser telling it to redirect the user to the referring page
header("Location: $link");
?>
  Permalink  
Comments
Prasad Khandekar 18-Apr-13 15:35pm
   
Don't post your question as solution. Instead use reply or Have a Question Or Comment button. What's the error?
bmw318mt 18-Apr-13 15:46pm
   
Notice: Use of undefined constant query - assumed 'query' in C:\wamp\www\My Web Sites v10d php\update_data.php on line 22
Call Stack
# Time Memory Function Location
1 0.0053 382024 {main}( ) ..\update_data.php:0

( ! ) Fatal error: Call to a member function bind_param() on a non-object in C:\wamp\www\My Web Sites v10d php\update_data.php on line 23
Call Stack
# Time Memory Function Location
1 0.0053 382024 {main}( ) ..\update_data.php:0
Prasad Khandekar 18-Apr-13 16:05pm
   
Thank's for pointing out the typo error. Is it working now?
bmw318mt 19-Apr-13 0:43am
   
no not working
Prasad Khandekar 19-Apr-13 5:53am
   
What error are you getting now? Please make sure that specification char in the bind_param call is correct. Refer to table on this (http://www.php.net/manual/en/mysqli-stmt.bind-param.php) page to know more about specification char.

Regards,

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web03 | 2.8.160208.1 | Last Updated 18 Apr 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100