Click here to Skip to main content
Rate this: bad
good
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.3K
Edited 18-Apr-13 6:28am
v2
Comments
Prasad Khandekar at 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 at 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 at 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 at 18-Apr-13 16:05pm
   
Thank's for pointing out the typo error. Is it working now?
bmw318mt at 19-Apr-13 0:43am
   
no not working
Prasad Khandekar at 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
0 DamithSL 400
1 Maciej Los 217
2 OriginalGriff 213
3 BillWoodruff 130
4 Zoltán Zörgő 85
0 OriginalGriff 7,953
1 DamithSL 6,139
2 Sergey Alexandrovich Kryukov 5,449
3 Maciej Los 5,293
4 Kornfeld Eliyahu Peter 4,539


Advertise | Privacy | Mobile
Web03 | 2.8.141223.1 | Last Updated 18 Apr 2013
Copyright © CodeProject, 1999-2014
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