Apostrophe insert DB question
Moderator: General Moderators
-
spacebiscuit
- Forum Contributor
- Posts: 390
- Joined: Mon Mar 07, 2005 3:20 pm
Apostrophe insert DB question
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.
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.
1-) Turn all magic_* off.
2-) Use http://www.php.net/mysql_real_escape_string.
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'";-
spacebiscuit
- Forum Contributor
- Posts: 390
- Joined: Mon Mar 07, 2005 3:20 pm
Hi,
Thanks for the reply.
I have turnedall magic_* off as instructed.
I tried adding the lines but I got these errors:
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.
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 470Any ideas?
Thanks......
Rob.
- AKA Panama Jack
- Forum Regular
- Posts: 878
- Joined: Mon Nov 14, 2005 4:21 pm
If I remember right you need to use double single quotes to escape it for ODBC.
Those are not double quotes before the 's but TWO single quotes for a total of three single quotes.
Code: Select all
$query=" INSERT INTO table VALUES 'Rob'''s' ";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));-
waqas_punjabian
- Forum Commoner
- Posts: 67
- Joined: Wed Aug 10, 2005 9:53 am
hi,
then to use data after fetching from db use this function:
regards
Waqas
may be this could work:$query=" INSERT INTO table VALUES 'Rob's' ";
Code: Select all
$st = "Rob's";
$st = addslashes($st);
$query=" INSERT INTO table VALUES '$st' ";Code: Select all
stripslashes($st);regards
Waqas
-
spacebiscuit
- Forum Contributor
- Posts: 390
- Joined: Mon Mar 07, 2005 3:20 pm
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:
- 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.
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);Working great now - thanks for your help!
Rob.