INSERT INTO issues with apostrophe

[chrislynch8]

Hi,

I have a Select statement that is returning multiple rows. THe information that is returned is getting inserted into a new table for an external applcation to access.

When values in the select statment contain apostrophe e.g. Chris O'Leary the created insert contain the following

 INSERT INTO table  (name) VALUES ('Chris O'Leary  ) 
  

See the issue - It inserts the data but the apostrophe is appearing as ' so the database field appears as Chris O'Leary - In sugar I see that it uses the following for apostrophe

 INSERT INTO table (name) VALUE ('Chris O\ 'Leary) 
  

For some reason the PHP code is not including the third apostrophe they should have ('Chris O'Leary') and ('Chris O\ 'Leary')

How do I acheive something like that is a logic hook? where my statment has something like teh following

 INSERT INTO table (name) VALUES ('$name') 
  

Where $name is returned in the select statment along with many other fields.

Rgds
Chris

[agcopley]

Hi,

I have a Select statement that is returning multiple rows. THe information that is returned is getting inserted into a new table for an external applcation to access.

When values in the select statment contain apostrophe e.g. Chris O'Leary the created insert contain the following

 INSERT INTO table  (name) VALUES ('Chris O'Leary  ) 
  

See the issue - It inserts the data but the apostrophe is appearing as ' so the database field appears as Chris O'Leary - In sugar I see that it uses the following for apostrophe

 INSERT INTO table (name) VALUE ('Chris O\ 'Leary) 
  

For some reason the PHP code is not including the third apostrophe they should have ('Chris O'Leary') and ('Chris O\ 'Leary')

How do I acheive something like that is a logic hook? where my statment has something like teh following

 INSERT INTO table (name) VALUES ('$name') 
  

Where $name is returned in the select statment along with many other fields.

Rgds
Chris

If one does a quick search in the manual you'll find a php function addslashes() that does this type of thing.

[chrislynch8]

Hi,

Thats doesn't work.

So my select statement is returning multiple rows, using

 while ($row = $this->bean->db->fetchByAssoc($results)
{
$name = $row['name'];
$address = $row['address'}


$query = "Insert into table (name, address) VALUE ('$name','$address')";

.
.
.
} 
  

I tried using the addslashes() for $name = addslashes($row['name']) but it stil went into my table as '

Rgds
Chris

Rgds
Chris

[Angel]

The escape character for SQL operations is, ironically, an apostrophe.

Thus, if you want to insert an apostrophe, it has to be referenced in this manner: VALUES ('Chris O''Leary' )

You should be able to easily address that via a string replace so as to replace the value of any of the columns from ' to '' before referencing it in your query.

BTW, this affects SELECT queries as well, not just inserts.

[agcopley]

The escape character for SQL operations is, ironically, an apostrophe.

Thus, if you want to insert an apostrophe, it has to be referenced in this manner: VALUES ('Chris O''Leary' )

You should be able to easily address that via a string replace so as to replace the value of any of the columns from ' to '' before referencing it in your query.

BTW, this affects SELECT queries as well, not just inserts.

The general character for escape operations in mysql is '\'.

addslashes() has and continues to work well for me :-).

Unless one enables NO_BACKSLASH_ESCAPES as sql mode in your mysql db, that is

Rgds
Andrew

[Angel]

Oops..thanks for that. Got caught up in the MSSQL world.

[agcopley]

Oops..thanks for that. Got caught up in the MSSQL world.

Naughty boy!

[Angel]

hahahahahahhahahahahahahhahah

[chrislynch8]

So how does the addslashes() work correctly for you in my case where should I be using it? Should I have it the way I posted above or should it be used elsewhere?

$name = addslashes($name) and then use $name in the INSERT statement?

Rgds
Chris