Topic: Query problem

[ober]

Yes, it is different where the object is the same.

[ober]

There is an additional '0D' after each of the ones that aren't coming out correctly, otherwise the hex is the same.

EDIT: from what I'm reading, that's part of a newline character.

[ober]

OK, I ran this on the database and it cleared the problem.  Now I just need to remove those on insertion.

UPDATE table SET field = REPLACE(REPLACE(field, '\r', ''), '\n', '')

[Mike]

There is an additional '0D' after each of the ones that aren't coming out correctly, otherwise the hex is the same.

EDIT: from what I'm reading, that's part of a newline character.

Perhaps this is part of the other topic you made about newline characters?

[ober]

Potentially, yeah.  The solutions are still separate though.

[ober]

OK, WTF:

Code: [Select]

$sym = trim(mysql_real_escape_string($pieces[0]));
$sym = str_replace(array("\r", "\r\n", "\n"), '', $sym);
Some of them are still being imported with 0D0A at the end when viewing the hex result.  What am I doing wrong?

[Mike]

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

Do the str_replace before the escape and trim.  That might be it

[ober]

Genius.  Not sure why it matters but that fixed it.

[Mike]

Well the escape is changing the singular character "\r" into two characters '\' and 'r'.

Code: [Select]

<?php
require_once 'default.inc.php';

$thestring = "This is\r\nthe string.";

$escape1 = mysql_real_escape_string($thestring);
$replace1 = str_replace(array("\n", "\r"), '', $escape1);

$replace2 = str_replace(array("\n", "\r"), '', $thestring);
$escape2 = mysql_real_escape_string($replace2);

echo '<pre>Before:   ', str_hex($thestring), "\nEscaped1: ", str_hex($escape1), "\nReplace1: ", str_hex($replace1);
echo "\nReplace2: ", str_hex($thestring), "\nEscaped2: ", str_hex($escape2), "\n$escape2";

function str_hex($str)
{
$ret = '';

for($i=0, $n=strlen($str); $i < $n; $i++)
$ret .= sprintf('%02x', ord($str[$i])) . ' ';

return $ret;
}
output:

Code: [Select]

Before:   54 68 69 73 20 69 73 0d 0a 74 68 65 20 73 74 72 69 6e 67 2e
Escaped1: 54 68 69 73 20 69 73 5c 72 5c 6e 74 68 65 20 73 74 72 69 6e 67 2e
Replace1: 54 68 69 73 20 69 73 5c 72 5c 6e 74 68 65 20 73 74 72 69 6e 67 2e
Replace2: 54 68 69 73 20 69 73 0d 0a 74 68 65 20 73 74 72 69 6e 67 2e
Escaped2: 54 68 69 73 20 69 73 74 68 65 20 73 74 72 69 6e 67 2e
This isthe string.

[micah]

hey Mike (or anyone really) -- not to hijack this thread but since you brought it up, can you help me understand the php mysql_real_escape_string() function?

I've been confused in the past as to when to use it....

Code: [Select]

<?php
// i'm under the impression that this doesn't work
$value = mysql_real_escape_string("my text value");
$sql = "INSERT INTO table (column) VALUES ('$value')";
mysql_query($sql);

// but this does
$value = "my text value";
mysql_query("INSERT INTO table (column) VALUES ('".mysql_real_escape_string($value).".') );
?>
this is difficult when building dynamic query statements.  What is the best practice for doing something like this:

Code: [Select]

<?php
$values = array("column1"=>"some value", "column4"=>"some other value", "etc"=>"you get the idea");
foreach($values as $column_name => $value){
  $column_list .= ", ".mysql_real_escape_string($column_name);
  $values_list .= ", '".mysql_real_escape_string($value)."'";
}
$sql = "INSERT INTO table ($column_list) VALUES ($values_list)";
mysql_query($sql);
?>

[ober]

I always write it the first way you have above because I always clean my data before I build the query.  Why wouldn't that work?

[micah]

ya know, this is the second time today I've been wrong about something.

I guess the thing that confused me was

Note:
A MySQL connection is required before using mysql_real_escape_string() otherwise an error of level E_WARNING is generated, and FALSE is returned.

I was given bad advice once and have been under the impression that this meant you could only use mysql_real_escape_string() inside a mysql_query() function.

Apparently it just means that you have to have an open mysql_connect() connection open at the time you make the mysql_real_escape_string() call.

I kinda feel dumb for not understanding that.

[ober]

Eh, I've done equally stupid shit in the past just because I didn't understand how something was supposed to work.

[Steve]

Who hasn't?