Page 1 of 1

Apostrophe insert DB question

Posted: Tue Nov 15, 2005 3:21 pm
by spacebiscuit
Hi,

I am sending data to a Topspeed Clarion database via the odbc function provided in php.

Everything works great until I try and insert with a string which contains an apostrophe, such as:

$query=" INSERT INTO table VALUES 'Rob's' ";

The apostrophe is fooling the db into thinking it is the string termination character. The soloution seem to add a slah with the addslashes function, so that the query becomes.

$query=" INSERT INTO table VALUES 'Rob\'s' ";

However this is not working, I have tried turning magic quotes both on and off via php.ini but whatever I try it will not work.

Any ideas or suggestion?

Thanks,

Rob.

Posted: Tue Nov 15, 2005 4:36 pm
by timvw
1-) Turn all magic_* off.
2-) Use http://www.php.net/mysql_real_escape_string.

Code: Select all

$name = "O'Reilly";
$cleansql_name = mysql_real_escape_string($name);
$query = "SELECT * FROM users WHERE name='$cleansql_name'";

Posted: Tue Nov 15, 2005 4:53 pm
by duk
then to display the information from the db check. stripslashes();

becouse the function mysql_real_escape_string for every ' will put this -> \'

Posted: Tue Nov 15, 2005 8:46 pm
by spacebiscuit
Hi,

Thanks for the reply.

I have turnedall magic_* off as instructed.

I tried adding the lines but I got these errors:

Code: Select all

Warning: mysql_real_escape_string(): Can't connect to MySQL server on 'localhost' (10061) in c:\inetpub\wwwroot\functions\waybill_routines.php on line 470

Warning: mysql_real_escape_string(): A link to the server could not be established in c:\inetpub\wwwroot\functions\waybill_routines.php on line 470
I think this function is specific to Mysql connections, I am connecting with the odbc_connect function. I have looked under the ODBC function list but cannot see an escape_string function there.

Any ideas?

Thanks......

Rob.

Posted: Tue Nov 15, 2005 9:56 pm
by AKA Panama Jack
If I remember right you need to use double single quotes to escape it for ODBC.

Code: Select all

$query=" INSERT INTO table VALUES 'Rob'''s' ";
Those are not double quotes before the 's but TWO single quotes for a total of three single quotes.

Posted: Wed Nov 16, 2005 6:45 am
by timvw
With odbc you can also use prepared statements and leave the escaping to the database driver ;)

Code: Select all

$lastname = "O'Reilly";
$firstname = "Tim";

$insert_stmt = "INSERT INTO users (lastname, firstname) VALUES (?, ?)";
$result = odbc_prepare($db, $insert_stmt);
odbc_execute($result, array($lastname, $firstname));

Posted: Wed Nov 16, 2005 7:00 am
by waqas_punjabian
hi,
$query=" INSERT INTO table VALUES 'Rob's' ";
may be this could work:

Code: Select all

$st = "Rob's";
$st = addslashes($st);
$query=" INSERT INTO table VALUES '$st' ";
then to use data after fetching from db use this function:

Code: Select all

stripslashes($st);

regards

Waqas

Posted: Wed Nov 16, 2005 7:31 am
by spacebiscuit
Hi,

Many thanks for the feedback, ok I have it working now and it seems that it is necessary to add a signle apostrophe in front of the apostrophe that I require to be written to the db.

I got round it by using this function:

Code: Select all

$variable=str_replace(" ' "," '' ",$variable);
- that's to search the string for an apostrophe, and where one is found replace it with 2. Of course Magic Quotes is switched off via the php.ini file.

Working great now - thanks for your help!

Rob.