O' last names and how do you SQL them?

[ThunderAI]

I have a few people with ' in their last names and I cant get the SQL statement to work correctly.  I have tried adding slashes, but if i manually add another ' in their lastname the SQL statement works, although I cant be certian its doing what its suppose to be doing.

 

What function should I use to parse the lastname to correct for odd chrs and correct them.

 

I have tried:

 

$tmp_last= addslashes($arr[0]);

$tmp_first= addslashes($arr[1]);

 

$tmp_last = mysql_real_escape_string($tmp_last, $link);

$tmp_first = mysql_real_escape_string($tmp_first, $link);

 

both do the same thing, but neither works.  Ideas?

[bluesoul]

If it's not working it's possible you have gpc_magic_quotes enabled in your php.ini which takes care of this for you.

[GingerRobot]

You don't need addslashes() - just use mysql_real_escape_string() and make sure magic_quotes is off.

 

bluesoul: magic_quotes isn't recommended.

[ThunderAI]

When I test for Magic Quotes the result is 1, so it is on. 

 

With it on, since I can't readily turn it off how do I proceed?

 

ALso the SQL line that uses this variable looks like this:

 

$nsql = '(`ruat_tsa_last_name` LIKE CONVERT(_utf8 \'%'.$tmp_last.'%\' USING latin1) COLLATE latin1_swedish_ci ';

 

 

[GingerRobot]

You can disable it in a variety of ways: http://uk2.php.net/manual/en/security.magicquotes.disabling.php

[bluesoul]

It wasn't an endorsement so much as a possible explanation as to why things may be not working.

 

You can use this to turn it off.

 

ini_set(magic_quotes_gpc, 0);

[PFMaBiSmAd]

ini_set() cannot turn off magic_quotes_qpc

 

It would take seeing your actual code that used mysql_real_escape_string() to be able to help you with why it did not work.

[ThunderAI]

<?php
$tmp_last			= ($arr[0]);
$tmp_first			= ($arr[1]);
$link 				= mysql_connect("-----", "-----", "-----", "----");
$tmp_last			= mysql_real_escape_string($tmp_last, $link);
$tmp_first			= mysql_real_escape_string($tmp_first, $link);


----

$nsql = '(`ruat_tsa_last_name` LIKE CONVERT(_utf8 \'%'.$tmp_last.'%\' USING latin1) COLLATE latin1_swedish_ci ';
$sql = $sql.$nsql;
$nsql = ' AND `ruat_tsa_first_name` LIKE CONVERT(_utf8 \'%'.$tmp_first.'%\' USING latin1) COLLATE latin1_swedish_ci ) ';
$sql = $sql.$nsql;

[PFMaBiSmAd]

Have you echoed the query string to make sure what is in it?

 

If magic_quotes_gpc is on, you must use stripslashes() on the data before you use mysql_real_escape_string()

[ThunderAI]

I have echoed the query using all the different methods, but i can not seem to make it work.

 

if i load the information right from the CSV and then into the array the ' goes right into the SQL statement and looks like

 

`ruat_tsa_last_name` LIKE CONVERT(_utf8 '%O'Dahl%' USING latin1) COLLATE latin1_swedish_ci

 

if I call the mysql_strip function without striping the slashes I get this:

 

`ruat_tsa_last_name` LIKE CONVERT(_utf8 '%O\'Dahl%' USING latin1) COLLATE latin1_swedish_ci

 

if I call the strip slashes after I do the Mysql_strip function I get the same result as the first.

 

No where does it add a second ' in the last name

[premiso]

I have echoed the query using all the different methods, but i can not seem to make it work.

 

if i load the information right from the CSV and then into the array the ' goes right into the SQL statement and looks like

 

`ruat_tsa_last_name` LIKE CONVERT(_utf8 '%O'Dahl%' USING latin1) COLLATE latin1_swedish_ci

 

if I call the mysql_strip function without striping the slashes I get this:

 

`ruat_tsa_last_name` LIKE CONVERT(_utf8 '%O\'Dahl%' USING latin1) COLLATE latin1_swedish_ci

 

if I call the strip slashes after I do the Mysql_strip function I get the same result as the first.

 

No where does it add a second ' in the last name

 

What is the mysql_error that is being returned?

[bluesoul]

I have echoed the query using all the different methods, but i can not seem to make it work.

 

if i load the information right from the CSV and then into the array the ' goes right into the SQL statement and looks like

 

`ruat_tsa_last_name` LIKE CONVERT(_utf8 '%O'Dahl%' USING latin1) COLLATE latin1_swedish_ci

 

if I call the mysql_strip function without striping the slashes I get this:

 

`ruat_tsa_last_name` LIKE CONVERT(_utf8 '%O\'Dahl%' USING latin1) COLLATE latin1_swedish_ci

 

if I call the strip slashes after I do the Mysql_strip function I get the same result as the first.

 

No where does it add a second ' in the last name

 

You shouldn't need multiple ' unless you're using a Sybase-style DB like SQL Server.  The second query looks right to me unless I'm missing something?

[Psycho]

If you are getting this data from a csv file, then all you need is to use mysql_real_escape_string() on the data before including it in the query. Showing some of your code will let us help you better.

[Andy-H]

<?php
$tmp_last   = $arr[0];
$tmp_first   = $arr[1];
$link     = mysql_connect("-----", "-----", "-----", "----");
if ( get_magic_quotes_gpc() == 1 ):
   stripslashes($tmp_last);
   stripslashes($tmp_first);
Endif;
$tmp_last   = mysql_real_escape_string($tmp_last, $link);
$tmp_first   = mysql_real_escape_string($tmp_first, $link);

----
$nsql = '(`ruat_tsa_last_name` LIKE CONVERT(_utf8 \'%'.$tmp_last.'%\' USING latin1) COLLATE latin1_swedish_ci ';
$sql .=$nsql;
$nsql = ' AND `ruat_tsa_first_name` LIKE CONVERT(_utf8 \'%'.$tmp_first.'%\' USING latin1) COLLATE latin1_swedish_ci ) ';
$sql .= $nsql;
?>

 

I'm out of my depth here but from reading the topic this seems to be what people have suggested...

[PFMaBiSmAd]

If you are getting this data from a file, magic_quotes_runtime being on would escape the data. You can turn magic_quotes_runtime off in your script. Which magic quotes setting where you referring to earlier?

 

And at this point full disclosure would be your best bet to get a quick solution. Post your full code and describe what you mean when you say it is not working.

[ThunderAI]

deleted

[ThunderAI]

<?php
SQL = SELECT * 
FROM tbl_nofly_add_list
WHERE (

`ruat_tsa_last_name` LIKE CONVERT( _utf8 '%O\'Dahl%'
USING latin1 ) 
COLLATE latin1_swedish_ci
AND `ruat_tsa_first_name` LIKE CONVERT( _utf8 '%Erick%'
USING latin1 ) 
COLLATE latin1_swedish_ci
)
OR (
`ruat_tsa_first_name` LIKE CONVERT( _utf8 '%O\'Dahl%'
USING latin1 ) 
COLLATE latin1_swedish_ci
AND `ruat_tsa_last_name` LIKE CONVERT( _utf8 '%Erick%'
USING latin1 ) 
COLLATE latin1_swedish_ci
)
OR (
`ruat_tsa_first_name` LIKE CONVERT( _utf8 '%Erick%'
USING latin1 ) 
COLLATE latin1_swedish_ci
AND `ruat_tsa_last_name` LIKE CONVERT( _utf8 '%O\'Dahl%'
USING latin1 ) 
COLLATE latin1_swedish_ci
)
OR (
`ruat_tsa_first_name` LIKE CONVERT( _utf8 '%Erick%'
USING latin1 ) 
COLLATE latin1_swedish_ci
AND `ruat_tsa_last_name` LIKE CONVERT( _utf8 '%O'Dahl % ' USING latin1) COLLATE latin1_swedish_ci ) OR (`ruat_tsa_last_name` LIKE CONVERT(_utf8 ' % Alan O \ 'Dahl%'
USING latin1 ) 
COLLATE latin1_swedish_ci
OR `ruat_tsa_first_name` LIKE CONVERT( _utf8 '%Erick Alan%'
USING latin1 ) 
COLLATE latin1_swedish_ci
)
OR (
`ruat_tsa_first_name` LIKE CONVERT( _utf8 '%Erick Alan%'
USING latin1 ) 
COLLATE latin1_swedish_ci
OR `ruat_tsa_last_name` LIKE CONVERT( _utf8 '%Alan O\'Dahl%'
USING latin1 ) 
COLLATE latin1_swedish_ci
)
ORDER BY ruat_tsa_sid<?php>

[ThunderAI]

I found some odd syntax in there, some of the ' dont have the leading \

[bluesoul]

And I'm gonna go out on a limb and say that's not the most efficient way of doing it, especially when some combination of PHP settings and string-escaping functions will do what you want it to do.

[ThunderAI]

And I'm gonna go out on a limb and say that's not the most efficient way of doing it, especially when some combination of PHP settings and string-escaping functions will do what you want it to do.

 

 

I am sure its not the best way of doing what I need to do; however, because of how dynamic this SQL statement has to be i dont know another way of doing it.  If there was I'd gladly do it, if i knew how.