Please help with apostrophes mysql

[lingo5]

Hi,

this is a piece of old code used to update a MySQL db (not done by me).

It has been working now on a client's site till today, when the text inserted doesn't admit apostrophes !!!!.

I know is something to do with mysql_real_escape_string....but don't know how to modify the code..please help !!.....

 

  //---- update text
  $sValors= "";
 for ($i=0; $i<count($aCampos); $i++){
  switch($aTipoCampo[$i]){
 case "string"  : $sValors .=$aCampos[$i]."='".prepararCadena(${$aCampos[$i]})."',";break;
 case "num"	 : $sValors .=$aCampos[$i]."=".PrepararDecimales((float)nz(${$aCampos[$i]},0)).","; break;
 case "boolean" : $sValors .=$aCampos[$i]."=".nz(${$aCampos[$i]},"0").",";  break;
  }
 }

  $sValors=removeSufix($sValors,",");
  $sSQL="update t_textos ".
	 "set $sValors ".
	 "where id_texto=$id";
textedebug( $sSQL);
   dat_execMant($sSQL);
  redirect($sUrlLista);
 }

[cyberRobot]

Have you looked through the user-defined functions? The prepararCadena() function, for example, looks like a good place to start.

[waynew]

Your code is open to SQL injection because you're not sanitizing your incoming data. mysql_real_escape_string() will help (if you're actually using the mysql_* functions). Example:

 

$clean = mysql_real_escape_string($unclean, $connection_link);

 

The best way to protect yourself against SQL injections would be to use prepared statements. Example with PDO:

 

<?php
$name = 'Wayne';
$value = 'Test';

$stmt = $db->prepare("INSERT INTO table_name (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);
$stmt->execute();
?>

Edited February 22, 2013 by waynewex

[lingo5]

Hi cyber Robot,

this is the code for the prepararCadena() function

should I use preg_replace?

function PrepararCadena($st)
{
$sRet = str_replace ("\\\'", "''", $st);
$sRet = str_replace ('\\\\\"', '"', $sRet);
$sRet  = str_replace("'", "\'", $sRet);
return $sRet;
}

[Jessica]

Dear god.

 

No. You should turn off magic quotes and use escape_string.

Really you should switch to PDO.

[cyberRobot]

Hi cyber Robot,

this is the code for the prepararCadena() function

should I use preg_replace?

 

It's a little difficult to tell what's going on without seeing the entire script (including the user-defined functions). Note that I don't have the time to do that.

 

What I would suggest is to review the various functions associated with updating the database entry. Once you have a good sense of how it's updating the database, look for a place to use mysql_real_escape_string(). You'll also want to figure out what's going on with the str_replace() portions of the code. It looks like the previous person was trying to manually fix things.

 

 

Side note: you may already be aware of this, but the mysql_ functions have been depreciated. At some point, you'll need to look into an alternative for the database connection.

http://php.net/manual/en/function.mysql-real-escape-string.php

[lingo5]

Thanks all !!

this seems to work ....and will have to do for now.

function PrepararCadena($st)
{
$sRet = str_replace ("'/", "''", $st);
$sRet = str_replace ('"/', '"', $sRet);
$sRet  = str_replace("'", "'/", $sRet);

return $sRet;
}

[Jessica]

How on earth did you come up with that as a solution?????? I'm just... I am literally flabbergasted. Literally.

 

What did you think you accomplished by switching not only the order of the escape character and escapee, but the actual slash from \ to / ?????

I just...

[lingo5]

Hi Jessica,

I'm just trying to get rid of a php error ...by doing this the error is gone.

I know this is not a solution but it will do for today until the new site I'm doing for this client is finished.

I didn't do this site and have no idea what the previous designer was doing...so I don't really have the time to go through the whole thing especially when the new site will be uptomorrow morning.

so....I don't really know what I did but seemed to do the trick...that's all I need now.

Sorry if I have given you a near heart attack.

Edited February 22, 2013 by lingo5

[AyKay47]