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:
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.