using REPLACE in a CONCAT

[rwigs]

I am using a query to pull from MySQL. In my PHP code, it looks like this:

$qry = "SELECT Name, CONCAT_WS(', ', location, address, city, state, zip) as location, Details...

But some of the records have a backslash (\) in the location in front of an apostrophe, so I am trying to use REPLACE to get rid of it. For example, the value in location might be 'Mom\'s house' and I want to just select it as 'Mom's house'. But I am not having any luck with this:

$qry = "SELECT Name, CONCAT_WS(', ', replace(location,'\',''), address, city, state, zip) as location, Details

Anyone know how to make that work?

[Barand]

SELECT Name, CONCAT_WS(', ', replace(location,'\\',''), address, city, state, zip) as location

If you have any Irish names in the data then they too could give the same problem.

 

I hope you now magic_quotes_gpc turned off

[NotionCommotion]

Originally, I was going to say this question belongs in the MySQL forum and not the PHP forum, but maybe not.  Why does the data in your database have a backslash before the apostrophe?

[rwigs]

Thanks for the reply. Unfortunately, I am still getting an error message for my syntax. Here is the complete query, if it makes a difference:

$qry = "SELECT Name, CONCAT_WS(', ', replace(location,'\\',''), address, city, state, zip) as location, Details, date_format(date, '%W, %M %e') as date
    , date as date2
    , Time_Start
    , Category
    , Papers
FROM
	Events
where
  id in ('".$events."')
order by
	date2
    , time_start";

[rwigs]

@NotionCommotion,

 

I was not sure which one to put it in either. It could go either way. But, I can get the query to work in MySQLWorkbench when accessing the database directly, but I cannot get it to work when using it in the PHP page. So I went with the PHP forum.

 

As for why they are in there, I am not sure. This is someone else's project that I have been asked to help out with because "You're an IT guy, you should be able to do this, right?"

[Barand]

  Why does the data in your database have a backslash before the apostrophe?

Usual cause is "magic_quotes"

 

• User inputs "O'ReiIly"
• Magic quotes turns this into "O\'Reilly"
• real_escape_string then changes it to "O\\\'Reilly"
• Gets written to db as "O\'Reilly"

[NotionCommotion]

@Barand,  Obviously one needs to escape for SQL injection.  Been using PDO and prepared statements for a while, so maybe I am getting lax.  I did a quick test and stored "mom's house" using PDO, and then went to the shell and viewed the data and it didn't have a backslash.  What am I missing?

 

@Rwigs.  Originally I thought you were escaping the HTML before inserting in your DB.  Typically you want to escape upon presentation.

[rwigs]

I'm not involved with the insertion part, that was done before I got asked to step into this. All I am trying to do is select the data to display. It works fine but just shows the \ before any apostrophe, so I am trying to figure out how to strip that out. Still can't get it to work, getting errors in my syntax.

[Ch0cu3r]

@NotionCommotion as Barand mentioned usual cause is magic quotes, which auto escapes quotes within user input variables such as $_GET, $_POST etc.

 

 

I'm not involved with the insertion part, that was done before I got asked to step into this. All I am trying to do is select the data to display. It works fine but just shows the \ before any apostrophe, so I am trying to figure out how to strip that out. Still can't get it to work, getting errors in my syntax.

@rwigs rather than do it in your query have you tried using stripslashes when you output the results from your query?

 

The best way to prevent this is to first find out why backslashes are being added to the quotes when inserted into your database. Barand has highlighted one possible case, another case (which I hope is not true) is the use of addslashes on the values being used in your insert query. This should never happen! If this is the case then you need to update your code to use MySQLi or PDO and use prepared queries

Edited June 5, 2015 by Ch0cu3r

[Barand]

@Barand,  Obviously one needs to escape for SQL injection.  Been using PDO and prepared statements for a while, so maybe I am getting lax.  I did a quick test and stored "mom's house" using PDO, and then went to the shell and viewed the data and it didn't have a backslash.  What am I missing?

 

@Rwigs.  Originally I thought you were escaping the HTML before inserting in your DB.  Typically you want to escape upon presentation.

It's the combination of magic_quotes ON and real_escape_string that causes the data to be escaped twice and "\'" being written to the db.

 

If magic_quotes is ON the slashes need to be stripped before using real_escape_string

[rwigs]

Using stripslashes in the output, rather than the query, did exactly what I needed to do.

 

Thanks!