I have been trying to fix a script that stopped working on hosting with magic quotes off.
There are two text fields (see code snippets below), I tried addslashes and mysql_real_escape_string, both worked well for $aDescr, but neither worked for $lTitle. Any help is appreciated.
if ($set_nl2br)
$lTitle=nl2br($aTitle);
else
$lTitle=$aTitle;
$aDescr = addslashes($aDescr);
$lTitle=addslashes($lTitle);
//sql query
What is the error you are getting?
what’s the value of $aTitle?
Sorry I didn’t make it clear. Either addslashes or mysql_real_escape_string only escapes $aDescr, but if I add a single quote in $aTitle, I will get the usual mysql error. Probably I missed something very simple, but I just couldn’t figure it out.
Can you show us your Query? As if I had to guess, your query is using $aTitle instead of $lTitle
Here is the complete function:
function createAd($aUserID,$aTitle,$aDescr,$aCat,$aExpireAfterDays,$aSpecial,$aPremium,$aExtraFields,$aNotifyAdmin)
{
global $ads_tbl,$cat_tbl,$url,$from_adress_mail,$set_nl2br;
$lExpireDate=addDaysToTimeStamp($aExpireAfterDays,time());
if ($set_nl2br)
$lTitle=nl2br($aTitle);
else
$lTitle=$aTitle;
if (!is_integer($aUserID))
failMsg("Critical Error","Owner ID was not integer");
if (!$aCat)
failMsg("Critical Error","Category ID missing");
if (!$lTitle)
failMsg("Critical Error","No title of ad");
if ($aExpireAfterDays<1)
failMsg("Critical Error","Expire After X days was 0, which is not allowed");
// Check if category is ad_is_validated
$sql="select cat_id from $cat_tbl where cat_id=$aCat";
$r=q($sql);
if (mysql_num_rows($r)<1)
failMsg("Critical Error","Category $aCat doesn´t exists!");
// remove ', added by Kevin
$aDescr = mysql_real_escape_string($aDescr);
$lTitle=addslashes($lTitle);
$sql="insert into $ads_tbl ";
$sql.=" (ad_owner,ad_title,ad_description,ad_date,ad_cat_id,ad_date_expire,ad_is_special,ad_is_premium)";
$sql.=" values(";
$sql.="$aUserID,'$lTitle','$aDescr',".time().",$aCat,$lExpireDate,$aSpecial,$aPremium)";
$res=q($sql);
$id=mysql_insert_id();
if ($id<1) // Auto Increment error (wrong db property)
failMsg("Critical Database Error","Field ad_id wasn´t increased. Check that AutoIncrement is on.");
if ($id>0)
{
addToHistory(6,$aUserID,$id,"");
userAdsCounter($aUserID,1); // Increase counter for this user
categoryCounter($aCat,1);
if ($aExtraFields)
{
$aExtraFields=ereg_replace(""",'"',$aExtraFields);
$aExtraFields=ereg_replace("'","\\'",$aExtraFields);
q("update $ads_tbl set ". substr($aExtraFields,0,-1)." where ad_id = $id");
}
if ($aNotifyAdmin==1)
{
// Notify administator that they have a new ad
$url = "http://" . $url . "/detail.php?id=$id";
$subject = formatString(LA_NEW_AD_INFO,array($id,$aTitle,$aDescr,$url,getRemoteIp()));
$body = formatString(LA_NEW_AD_INFORM,array($id,$aTitle,$aDescr,$url,getRemoteIp()));
sendEmail($from_adress_mail,$from_adress_mail,$subject,$body);
}
return $id;
}
}
Umm, what gets printed if you do a
$sql="insert into $ads_tbl ";
$sql.=" (ad_owner,ad_title,ad_description,ad_date,ad_cat_id,ad_date_expire,ad_is_special,ad_is_premium)";
$sql.=" values(";
$sql.="$aUserID,'$lTitle','$aDescr',".time().",$aCat,$lExpireDate,$aSpecial,$aPremium)";
echo $sql;
The system is using templates, It’s a bit troublesome to print out $sql. (Is there an easy way to print out string except writing to a log file?)
The error messages are very clear, if there is a single quote in the title, it prints sql error:
Invalid MySql query
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’ AND ad_owner=113’ at line 1
If I remove the quote, it works well. The description is escaped correctly, it always works with or without quotes.
On second thought, I really need an extra set of eyes. I do not see AND ad_owner= anywhere in the code you posted. You must figure out a way to print out queries before they execute if you are seriously interested in debugging your code.
I think it’s this update that’s giving you errors, but who knows what the q() function does 
q("update $ads_tbl set ". substr($aExtraFields,0,-1)." where ad_id = $id");
Anyway, you really should consider changing from mysql_ to mysqli_ (or even pdo): http://www.php.net/manual/en/intro.mysql.php
And if that isn’t possible, use mysql_real_escape_string to escape the string values when using them in a query, and not addslashes.
Edit: on second thought I don’t think that line is causing the error. Like coder911 says, I do not see AND ad_owner= anywhere in the code you posted.
I added logging and it solved the problem. The title is also used before ‘createAd’, the error actually happens elsewhere before the ad is inserted into db. It’s very silly of me to take things for granted without debugging, especially with unfamiliar code. Many thanks for everyone’s help and patience, I really appreciate it.