Jump to content

Possible SQL injection error?


Fermac
Go to solution Solved by Fermac,

Recommended Posts

Hi all,

   I am new to this, so please be gentle :) . I have just taken over a website and moved to new server and it has thrown up a couple of errors, most of which I've sorted other than this one:

 

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 '' at line 1

 

this comes up when trying to update a quantity in a shopping cart and I think it has something to do with SQL injection but I've no idea what to do. My two bits of code are below, first the page with the drop down menu on it:

<?php include 'include/functions.php';
$colname_rsOrderInformation = "-1";
if (isset($_SESSION['cartId'])) {
  $colname_rsOrderInformation = $_SESSION['cartId'];
}
mysql_select_db($database_conDB, $conDB);
$query_rsCartInformation = sprintf("SELECT * FROM tabCart WHERE cartId = %s", GetSQLValueString($colname_rsOrderInformation, "int"));
$rsCartInformation = mysql_query($query_rsCartInformation, $conDB) or die(mysql_error());
$row_rsCartInformation = mysql_fetch_assoc($rsCartInformation);
$totalRows_rsCartInformation = mysql_num_rows($rsCartInformation);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="keywords" content="belfast, cookery, school, fish, seafood, learning, northern ireland" />
<meta name="description" content="Belfast Cookery School in association with Mourne Seafood Bar - More than just a restaurant" />
<title>Belfast Cookery School in association with Mourne Seafood Bar - More than just a restaurant - Gift Vouchers</title>
<link rel="shortcut icon" href="favicon.ico" />
<link href="main.css" rel="stylesheet" type="text/css"/>

<style type="text/css">
.slideshow { height:319px; width:476px; margin-right:0px; margin-bottom:0px;  z-index:0; background-color: #fff;}
.slideshow img { height: 319px !important; width: 476px !important; padding-left: 0px;  }
</style>
<!-- include jQuery library -->
<script type="text/javascript" src="include/jquery.min.js"></script>
<!-- include Cycle plugin -->
<script type="text/javascript" src="include/java.js"></script>
<script type="text/javascript">
$(document).ready(function() {
    $('.slideshow').cycle({
		fx: 'fade' // choose your transition type, ex: fade, scrollUp, shuffle, etc...
	});
});
</script><?php if (!isset($_SESSION['memUsername'])) {?>
<script type="text/javascript">
function changeToPassword(){
document.getElementById("txtLogPassword").type = "password";
document.getElementById("txtLogPassword").value = ""
}

function resetPassword() {
	if (document.getElementById("txtLogPassword").value == "") {
		document.getElementById("txtLogPassword").type = "text";
		document.getElementById("txtLogPassword").value = "Password";
	}
}
</script>
<?php }?>
<script type="text/javascript">


  var _gaq = _gaq || [];
  _gaq.push(['_setAccount', 'UA-9218136-12']);
  _gaq.push(['_trackPageview']);


  (function() {
    var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
    ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
    var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
  })();


</script>

Now the update function that's called:

<?php 
for ($x=1; $x<=$_POST['hidTotalCartItems']; $x++) {
	$tempCartQty = "selQty".$x;
	$newCartQty = @$_POST[$tempCartQty];
	
	$tempCartItemId = "hidCartRowId".$x;
	$cartItemId = @$_POST[$tempCartItemId];
	
	$tempCartItemPrice = 'hidCartPrice'.$x;
	$cartItemPrice = @$_POST[$tempCartItemPrice];
	
mysql_select_db($database_conDB, $conDB);
$query_rsUpdateCartLine = sprintf("SELECT * FROM tabCart WHERE Id = %s", $cartItemId);
$rsUpdateCartLine = mysql_query($query_rsUpdateCartLine, $conDB) or die(mysql_error());
$row_rsUpdateCartLine = mysql_fetch_assoc($rsUpdateCartLine);
$totalRows_rsUpdateCartLine = mysql_num_rows($rsUpdateCartLine);

if ($newCartQty>0) {
	$newCartItemPrice = $newCartQty * $cartItemPrice;
	
	$updateSQL = sprintf("UPDATE tabCart SET cartQty=%s, cartTotal=%s WHERE Id = %s",
                       GetSQLValueString(@$newCartQty, "int"),
                       GetSQLValueString(@$newCartItemPrice, "double"),
					   GetSQLValueString($cartItemId, "int"));

  mysql_select_db($database_conDB, $conDB);
  $Result1 = mysql_query($updateSQL, $conDB) or die(mysql_error());
  
  mysql_free_result($rsUpdateCartLine);
}
	
	if ($newCartQty==0) {
		$deleteSQL = sprintf("DELETE FROM tabCart WHERE Id = %s",
					   GetSQLValueString($cartItemId, "int"));

  mysql_select_db($database_conDB, $conDB);
  $Result1 = mysql_query($deleteSQL, $conDB) or die(mysql_error());
	}

}

?>

Anyone got any ideas what I'm doing wrong? When the voucher quantity is changed and update button clicked, that's when the error occurs. 

Link to comment
Share on other sites

If your not injecting SQL, it's not an SQL injection error, this is most likely a GIGO error - change your update error message to the following and we'll see exactly what's going on with your SQL (just remeber and post it up ;) ).

 

 

$Result1 = mysql_query($updateSQL, $conDB) or die($updateSQL."<br><br>Caused an error on server, that error was :<br><br>".mysql_error());
Link to comment
Share on other sites

Hi Muddy, thanks for quick response, told you I didn't know what I was talking about! Only thing comes up when I do that is:

Caused an error on server, that error was :

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 '' at line 1
Link to comment
Share on other sites

there's really no way that the posted code would not display something for $updateSQL in the error output. are you 100% sure the posted code is what is actually running on the server?

 

also, since the mysql_query() statements, in question, are using the $conDB connection variable, you must use that in the mysql_error() statements to insure that the error message corresponds to the last query that was executed on that connection. i'm thinking the mysql_error message you are seeing is left over from some login code or similar that is using a different connection.

 

i would also recommend that you set php's error_reporting to E_ALL and display_errors to ON to get php to help you by reporting and displaying all the errors it detects.

Link to comment
Share on other sites

this is not a versioning problem - this is a coding problem.

 

what mac_gyver was saying about the $conDB is that you need to put it inside the parenthesis of the mysql_error($conDB) <-- like that to make sure it is showing the relevent error from the server.

 

what you also need to do is turn on error reporting (to report on all errors) and enable display_errors in order to effictvly debug your code.  These should be turned on whenever you are developing any code (be it a new write or changes to an existing script) to make sure everything is running correctly.

Link to comment
Share on other sites

Sorry I wasn't more specific, yes I changed it to 

 

mysql_error($conDB)

 

and then that's when the "Query was empty" message came up. This was a system inherited from someone else. I'm aware coding isn't great on it, but don't know enough about this to fix. I did add this to my .htaccess file as described by my host to help:

php_flag display_errors on
php_value error_reporting E_ALL

But it still came up with the same "Query was empty" message.

Link to comment
Share on other sites

This is what appears:

Caused an error on server, that error was :

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 '' at line 1

when running with a $newCartQty of 1
Link to comment
Share on other sites

Sorry, here it is:

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}
Link to comment
Share on other sites

the time you got the 'query was empty' error is the only thing i believe in this thread. that the error message changed back to the original, indicates your code where the error is occurring at isn't what you have been posting (it's likely that one of the other queries is failing now and reporting a previous mysql_error because another mysql_error() statement doesn't have the $conDB in it.)

 

post the complete current code for the page, showing the <?php ?> tags it it as well (less any database credentials). there's something going on in your code on that page and to save time just post the whole thing.

 

the reason your code worked on one server and not another is because your code is dependent on some server specific setting or feature. it's also dreamweaver produced code and the DW code is crappy.

 

one of the reasons the error reporting setting didn't work is the E_ALL defined constant doesn't exist in the .htaccess file. you need to use php_value error_reporting -1

Link to comment
Share on other sites

Okay, here's the shopping cart page in its entirety:

<?php include 'include/functions.php';
$colname_rsOrderInformation = "-1";
if (isset($_SESSION['cartId'])) {
  $colname_rsOrderInformation = $_SESSION['cartId'];
}
mysql_select_db($database_conDB, $conDB);
$query_rsCartInformation = sprintf("SELECT * FROM tabCart WHERE cartId = %s", GetSQLValueString($colname_rsOrderInformation, "int"));
$rsCartInformation = mysql_query($query_rsCartInformation, $conDB) or die(mysql_error());
$row_rsCartInformation = mysql_fetch_assoc($rsCartInformation);
$totalRows_rsCartInformation = mysql_num_rows($rsCartInformation);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<link rel="shortcut icon" href="favicon.ico" />
<link href="main.css" rel="stylesheet" type="text/css"/>

<style type="text/css">
.slideshow { height:319px; width:476px; margin-right:0px; margin-bottom:0px;  z-index:0; background-color: #fff;}
.slideshow img { height: 319px !important; width: 476px !important; padding-left: 0px;  }
</style>
<!-- include jQuery library -->
<script type="text/javascript" src="include/jquery.min.js"></script>
<!-- include Cycle plugin -->
<script type="text/javascript" src="include/java.js"></script>
<script type="text/javascript">
$(document).ready(function() {
    $('.slideshow').cycle({
		fx: 'fade' // choose your transition type, ex: fade, scrollUp, shuffle, etc...
	});
});
</script><?php if (!isset($_SESSION['memUsername'])) {?>
<script type="text/javascript">
function changeToPassword(){
document.getElementById("txtLogPassword").type = "password";
document.getElementById("txtLogPassword").value = ""
}

function resetPassword() {
	if (document.getElementById("txtLogPassword").value == "") {
		document.getElementById("txtLogPassword").type = "text";
		document.getElementById("txtLogPassword").value = "Password";
	}
}
</script>
<?php }?>
<script type="text/javascript">


  var _gaq = _gaq || [];
  _gaq.push(['_setAccount', 'UA-9218136-12']);
  _gaq.push(['_trackPageview']);


  (function() {
    var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
    ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
    var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
  })();


</script>
</head>
<body>
<div id="head">
	<div id="header"><img src="images/logo.png" alt="" title="" />
	<a href="http://www.hgffghghf.com" target="_blank"><img src="images/aaa.png" alt="ghffgh" class="jhghjj" border="0" /></a>
	<div id="nav">
	<a href="index.php">Home</a>
	<a href="about.php">About Us</a>
	<a href="chefs.php">Chefs</a>
	<a href="classes.php">Cookery Classes</a>
	<span class="brown">Gift Vouchers</span>
	<a href="testimonials.php">Testimonials</a> <a  href="faqs.php">FAQ's</a>
	<a href="contact.php">Contact Us</a></div>
	<div id="contact"><p>T: 02890 234 722<br />E: <a href="mailto:info@belfastcookeryschool.com">info@belfastcookeryschool.com</a></p>
    <?php include 'include/memberstuff.php';?>
    </div>
	</div>
</div>

<div id="content">
<div class="contentholder">
	<div class="contentleft">
	<h1>shopping cart</h1>
	<p>Your current shopping cart is shown below.</p>
	<?php include 'include/shopping-cart.php';?>
	</div>
	<div class="contentright">
	<div class="slideshow">
	<img src="images/homeimg1.jpg" alt="" />
	<img src="images/homeimg2.jpg" alt="" />
	<img src="images/homeimg3.jpg" alt="" />
	</div>
	</div>
	</div>
</div>

<div id="content2">
<div class="contentholder">
	<div class="contentleft2">
	
	<?php include("include/classes.php"); ?> 
	<br style="clear:both;" />
	<?php include("include/chefs.php"); ?> 
	
	</div>
	
	<div class="contentright2">
	<?php include("include/twitter.php"); ?>
	</div>

</div>
</div>

<?php include("include/footer.php"); ?> 
</body>
</html>

Next is the /includes/functions.php

<?php require_once('Connections/conDB.php');
session_start();
date_default_timezone_set('Europe/Belfast');
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

if (!isset($_SESSION['cartId'])) {
	@$_SESSION['cartId'] = date('U').rand(1000,9999);	
}

mysql_select_db($database_conDB, $conDB);
$query_rsSite = "SELECT * FROM tabSite";
$rsSite = mysql_query($query_rsSite, $conDB) or die(mysql_error());
$row_rsSite = mysql_fetch_assoc($rsSite);
$totalRows_rsSite = mysql_num_rows($rsSite);

$url = $row_rsSite['siteURL'];

$colname_rsMemberDetails = "-1";
if (isset($_SESSION['memUsername'])) {
  $colname_rsMemberDetails = $_SESSION['memUsername'];
}
mysql_select_db($database_conDB, $conDB);
$query_rsMemberDetails = sprintf("SELECT * FROM tabMembers WHERE cusEmail = %s", GetSQLValueString($colname_rsMemberDetails, "text"));
$rsMemberDetails = mysql_query($query_rsMemberDetails, $conDB) or die(mysql_error());
$row_rsMemberDetails = mysql_fetch_assoc($rsMemberDetails);
$totalRows_rsMemberDetails = mysql_num_rows($rsMemberDetails);

if ($totalRows_rsMemberDetails>0 AND $row_rsMemberDetails['cusMedical']!='Y' AND !isset($_SESSION['promptdisplayed'])) {
	$_SESSION['mustsign']="true";	
}

$customerCredits = @$row_rsMemberDetails['cusCredit'];

// WHO'S ONLINE
if (!isset($_SESSION['cartId'])) {
	$_SESSION['cartId']=date('U').rand(1000,9999);
}

$colname_rsBooking = "-1";
if (isset($_SESSION['cartId'])) {
  $colname_rsBooking = $_SESSION['cartId'];
}
mysql_select_db($database_conDB, $conDB);
$query_rsBooking = sprintf("SELECT * FROM tabBookings WHERE bookBookingReference = %s", GetSQLValueString($colname_rsBooking, "text"));
$rsBooking = mysql_query($query_rsBooking, $conDB) or die(mysql_error());
$row_rsBooking = mysql_fetch_assoc($rsBooking);
$totalRows_rsBooking = mysql_num_rows($rsBooking);

$colname_rsOpenOrder = "-1";
if (isset($_SESSION['cartId'])) {
  $colname_rsOpenOrder = $_SESSION['cartId'];
}
mysql_select_db($database_conDB, $conDB);
$query_rsOpenOrder = sprintf("SELECT * FROM tabOrders WHERE orderId = %s", GetSQLValueString($colname_rsOpenOrder, "text"));
$rsOpenOrder = mysql_query($query_rsOpenOrder, $conDB) or die(mysql_error());
$row_rsOpenOrder = mysql_fetch_assoc($rsOpenOrder);
$totalRows_rsOpenOrder = mysql_num_rows($rsOpenOrder);

function urlreplace($name) {
include 'includes/find_and_replaces.php';
return strtolower(str_replace($find, $replace, $name));
}

function reverseurl($name) {
include 'includes/find_and_replaces.php';
return ucwords(str_replace($replace, $find, $name));
}

 function selfURL() { 
$s = empty($_SERVER["HTTPS"]) ? '' : ($_SERVER["HTTPS"] == "on") ? "s" : ""; 
$protocol = strleft(strtolower($_SERVER["SERVER_PROTOCOL"]), "/").$s; 
$port = ($_SERVER["SERVER_PORT"] == "80") ? "" : (":".$_SERVER["SERVER_PORT"]); 
return $protocol."://".$_SERVER['SERVER_NAME'].$port.$_SERVER['REQUEST_URI']; 
} 

function strleft($s1, $s2) { 
return substr($s1, 0, strpos($s1, $s2)); 
}

$browesrtype = $_SERVER['HTTP_USER_AGENT'];
if (strpos($browesrtype, "MSIE 6.0")>0) $browser = "IE";

// ** Logout the current user. **
$logoutAction = $_SERVER['PHP_SELF']."?doLogout=true";
if ((isset($_SERVER['QUERY_STRING'])) && ($_SERVER['QUERY_STRING'] != "")){
  $logoutAction .="&". htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_GET['doLogout'])) &&($_GET['doLogout']=="true")){
  //to fully log out a visitor we need to clear the session varialbles
  $_SESSION['memUsername'] = NULL;
  $_SESSION['mustsign'] = NULL;
  $_SESSION['promptdisplayed'] = NULL;
  unset($_SESSION['memUsername']);
  unset($_SESSION['mustsign']);
  unset($_SESSION['promptdisplayed']);
  header("LOCATION: /");
  exit;
}

// REDIRECTS
if (@$_POST['hidAction']=="bookclass") include 'functions/f_bookclass.php';
if (@$_POST['hidAction']=='register') include 'functions/f_register.php';
if (@$_POST['hidAction']=="cancelbooking") include 'functions/f_cancelbooking.php';
if (@$_POST['hidAction']=='login') include 'functions/f_login.php';
if (@$_POST['hidAction']=='updatebooking') include 'functions/f_update_booking.php';
if (@$_POST['hidAction']=='makepayment' AND @$_POST['button']=="Apply") include 'functions/f_checkcard.php';
if (@$_POST['hidAction']=='makepayment' AND @$_POST['button']=="Pay Now") include 'functions/f_makepayment.php';
if (@$_POST['hidAction']=='makepayment' AND @$_POST['button']=="Update Price") include 'functions/f_checkdiscount.php';
if (@$_POST['hidAction']=='buydiscount1') include 'functions/f_buydiscount1.php';
if (@$_POST['hidAction']=='buydiscount2') include 'functions/f_update_order.php';
if (@$_POST['hidAction']=='makepaymentdiscount') include 'functions/f_makepayment_cards.php';
if (@$_POST['hidAction']=='registercode') include 'functions/f_checkcard.php';
if (@$_POST['hidAction']=='resetpassword') include 'functions/f_resetpassword.php';
if (@$_POST['hidAction']=='updatedetails') include 'functions/f_update_account.php';
if (@$_POST['hidAction']=='addorder' AND @$_POST['button']=="Checkout") include 'functions/f_add_order.php';
if (@$_POST['hidAction']=='addorder' AND @$_POST['button']=="Update") include 'functions/f_updatecart.php';

mysql_free_result($rsSite);
?>

and here is the f_updatecart.php file:

<?php 
for ($x=1; $x<=$_POST['hidTotalCartItems']; $x++) {
	$tempCartQty = "selQty".$x;
	$newCartQty = @$_POST[$tempCartQty];
	
	$tempCartItemId = "hidCartRowId".$x;
	$cartItemId = @$_POST[$tempCartItemId];
	
	$tempCartItemPrice = 'hidCartPrice'.$x;
	$cartItemPrice = @$_POST[$tempCartItemPrice];
	
mysql_select_db($database_conDB, $conDB);
$query_rsUpdateCartLine = sprintf("SELECT * FROM tabCart WHERE Id = %s", $cartItemId);
$rsUpdateCartLine = mysql_query($query_rsUpdateCartLine, $conDB) or die(mysql_error());
$row_rsUpdateCartLine = mysql_fetch_assoc($rsUpdateCartLine);
$totalRows_rsUpdateCartLine = mysql_num_rows($rsUpdateCartLine);

if ($newCartQty>0) {
	$newCartItemPrice = $newCartQty * $cartItemPrice;
	
	$updateSQL = sprintf("UPDATE tabCart SET cartQty=%s, cartTotal=%s WHERE Id = %s",
                       GetSQLValueString(@$newCartQty, "int"),
                       GetSQLValueString(@$newCartItemPrice, "double"),
					   GetSQLValueString($cartItemId, "int"));

  mysql_select_db($database_conDB, $conDB);
  $Result1 = mysql_query($updateSQL, $conDB) or die(mysql_error());
  
  mysql_free_result($rsUpdateCartLine);
}
	
	if ($newCartQty==0) {
		$deleteSQL = sprintf("DELETE FROM tabCart WHERE Id = %s",
					   GetSQLValueString($cartItemId, "int"));

  mysql_select_db($database_conDB, $conDB);
  $Result1 = mysql_query($deleteSQL, $conDB) or die(mysql_error());
	}

}

?>

These are the three files I was surmising were the issue.

Link to comment
Share on other sites

the current code doesn't have the debugging logic in it that has been suggested (and in the case of adding $conDB to the mysql_error($conDB) statements, this is a requirement to insure that the error being displayed is for the query that was just ran on that connection.) why did you take the debugging logic out, you are not finished debugging the problems in the code.

 

here's some more suggestions -

 

1. make sure php's error_reporting/display_errors are set as suggested and actually working.

 

2. remove ALL the @ error suppressors in the code. all they are doing is hiding the problems that would provide an answer to 'why did this code work on the previous host and not on the current one?' i even noticed some @'s in code where there will never be an error.

Edited by mac_gyver
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.