|
PHP - Escaping Apostrophe Using Php/mssql And Odbc
I am using PHP with ODBC to connect to an existing MSSQL database to query for some names. When I query a name that has an apostrophe in it, I get an error.
Example typing O'Malley as the name: Quote Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'malley'., SQL state 37000 in SQLExecDirect I have used addslashes to the string but get the same result: Code: [Select] $string = addslashes($_POST['string']); $connect = odbc_connect("$mssql_name", "$mssql_user", "$mssql_pass"); $query_mssql = "SELECT pr.NameFirst, pr.NameLast, pr.NameMiddle, pr.Birthdate, p.Barcode, p.OrganizationID FROM PatronRegistration pr JOIN Patrons p ON (pr.PatronID = p.PatronID) WHERE pr.NameLast = '$string'"; $result = odbc_exec($connect, $query_mssql); Any ideas? Similar TutorialsI could really use some help, I'm a new programmer so I'm open to any advice or ideas, but we've created a VFP program and here's what I need... I need to make an HTML page for people to input information about a contract and then upload files that are associated with that contract. This all needs to be done over the internet of course and I'm not sure if there's a software that would help with this or if ODBC is even the way to go? Right now I'm coming up awfully blank with ODBC... Any suggestions? Hi guys, I wonder if somebody can help me? When a user enters details about themselves into a textbox and includes an apostrophe, when the profile is pulled from the MYSQL database the apostrophe is replaced with a \' How can I get it so when the profile is viewed there is either no backslash Thanks for any help Regards Rob Hi ,
I have a website which uses apostrophe in merchant names (craig's) and Product name (Fresh goat's).
If I try click on the search pages using these names with apostrophe then it displays the following error.
Error: SELECT * FROM merchant WHERE user_name='Major_Craig's_Chutney' && is_active='1'
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's_Chutney' && is_active='1'' at line 1
I tried to add an apostrophe in php file of merchant search but still it shows up the same error or empty page.
can anyone help me on this????
I'm a little rusty on my PHP skills, so this (probably simple) problem has me stumped. I have a search engine which retrieves from a database with titles, of which some of those titles have apostrophes within them. There can also be several authors for a single title, so I've set up this code to retrieve and give each author a different url to their page: Code: [Select] $select_author = mysql_query("SELECT DISTINCT author FROM archives WHERE `title` = '$title' AND `year` = '$year' AND `category` = '$category' AND `group` = '$group' ") or die(mysql_error()); However, whenever an apostrophe appears for the $title I get this error: Quote You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'll Have Lemonade, Please' AND `year` = '2002' AND `category` = 'Lemon' AND `grou' at line 1 The full title is "I'll Have Lemonade, Please," and you can see where the problem lies. I've tried replacing the "`title` = '$title'" with "`title` = '%s'", but then none of the authors will appear. Anyone have any suggestions on how to fix this problem? Thanks in advance, and here's the full code: Code: [Select] <?php if(isset($_POST[search])) { $title = strtolower(strip_tags(mysql_escape_string($_POST['title']))); $author = strtolower(strip_tags(mysql_escape_string($_POST['author']))); $summary = strtolower(strip_tags(mysql_escape_string($_POST['summary']))); $category = strip_tags(mysql_escape_string($_POST['category'])); $group = strip_tags(mysql_escape_string($_POST['group'])); $rating = strip_tags(mysql_escape_string($_POST['rating'])); $year = strip_tags(mysql_escape_string($_POST['year'])); $termsArray = array(); if(!empty($author)) { $termsArray[] = "author LIKE '%$author%'"; } if(!empty($title)) { $termsArray[] = "title LIKE '%$title%'"; } if(!empty($summary)) { $termsArray[] = "summary LIKE '%$summary%'"; } if (count($termsArray) > 0){ $terms = implode(" AND ", $termsArray); $terms = " WHERE ".$terms; unset($termsArray); //clear memory, cause we're finished using this. } $join = (empty($title) && empty($author) && empty($summary)) ? "WHERE" : "AND"; $sql_category = ($category == all) ? "" : "$join `category`='$category'"; if ($sql_category != ""){ $join = "AND"; } $sql_group = ($group == all) ? "" : "$join `group`='$group'"; if ($sql_group != ""){ $join = "AND"; } $sql_rating = ($rating == all) ? "" : "$join `rating`='$rating'"; if ($sql_rating != ""){ $join = "AND"; } $sql_year = ($year == all) ? "" : "$join year='$year'"; $qSearch = "SELECT * FROM archives $terms $sql_category $sql_group $sql_rating $sql_year GROUP BY url ORDER BY title ASC, author ASC"; $rsSearch = mysql_query($qSearch) or die(mysql_error()); $end = ''; if (mysql_num_rows($rsSearch) >= 2) { $end = 's'; } if (mysql_num_rows($rsSearch) == 0) { print '<p>Sorry, there were no results returned for your search. Please try again.</p>'; } else { print '<center><p><b>'.mysql_num_rows($rsSearch).'</b> title'.$end.' found.</p></center>'; echo '<ol>'; while ($row = mysql_fetch_array($rsSearch)) { extract($row); $select_author = mysql_query("SELECT DISTINCT author FROM archives WHERE `title` = '$title' AND `year` = '$year' AND `category` = '$category' AND `group` = '$group' ") or die(mysql_error()); $aut = ""; while ($row3 = mysql_fetch_array($select_author)) { $aut .= "<a href=\"author.php?author={$row3[author]}\">$row3[author]</a> & "; } $aut = substr($aut,0,-3); $my_code = urlencode($title); $my_author = urlencode($author); echo '<li><a href="info.php?author='.$my_author.'&title='.$my_code.'">'.$title.'</a> by '.$aut.'</li>'; } echo '</ol>'; } } ?> I have the following code to search my database (obviously some of the surrounding code is not provided) but I hope this provides enough for me to be clear about my problem) : $sql="SELECT * FROM member_details WHERE state='$state' AND location='$locn' ORDER BY Surname, Given_name"; $result=mysqli_query($conn, $sql) or die("Error in selection -".mysqli_error($conn)); $numrows=mysqli_num_rows($result); if($numrows==0) { echo "There are no members listed in this State/Territory/location."; } else { while($row=mysqli_fetch_array($result)) { $surname=$row['Surname']; if (strstr($surname, "'")) echo "yes"; else echo "no"; } } This works fine if I am searching for a surname that contains a letter such as ''a". However when I search on the apostrophe, even though I know I have several surnames in the database which contain the apostrophe, I get a 'no' response for all of them. Thank you. Can anyone see what I am doing wrong here please or suggest a different approach? Note that my code still use the old mysql query and I am aware of that. I want to migrate it to mysqli soon but it will have to wait until I learn how to do so. Meanwhile, please help me with the code that I have now. Thank you
I've looked around the internet to find how to do this and I found two methods:
htmlspecialchars();
and
mysql_real_escape_string();
I wrapped my text into one of the two function in order to pass in text with apostrophe in it to the database.
However I encountered a problem.
With mysql_real_escape_string, I got the desired effect that I want but the problem is that, the function also apply its effect onto html img tag. So <img src="test"> turns into <img src=/"test/"> and therefore images (and also links) will not appear.
With htmlspecialchars, again I got the desired function that I want which is to pass in apostrophe into the database. The problem with this is that when I pull the data out from the database and echo it onto my page, it doesn't render and show as a plain html code.
What confuse me a lot is that, it works fine inside my localhost.
Please help
Thank you!
Attached Files
content-insert.php 3.92KB
4 downloads Hi Friends, I am Anes I want to ensure all surnames are uppercase in php I use $surname = ucfirst(strtolower($dataField)); How do I ensure that any names such as O'Brien, don't result in O'brien thanks Hi Guys I have this PHP Upload Scripts below which uploads file of customers into the customers folder and at the same time inserts the file path into the database. The problems is for name like O'hare or O'neil its uploads into the customers folder but does not insert the file path into the database - probably because of the " ' " apostrophe From the code below is there anyway I can deal with this issue? Thanks alot Code: [Select] <?php //This php block of code will takecare of inserting the upload variables into the db if(isset($_POST['submitbutton'])) { $target_path = 'customerUploads/' . $check_id . ', ' . $c_name . '/'; $target_path = $target_path . basename( $_FILES['upload']['name']); $manager= mysql_real_escape_string($_POST['username']); $upload = $_FILES['upload']['name']; $check_id = mysql_real_escape_string($_POST['id']); $submitbutton= mysql_real_escape_string($_POST['submitbutton']); if($submitbutton) { if($manager&&$upload) { if (file_exists($target_path)) { echo $_FILES["upload"]["name"] . " already exists. "; } else { move_uploaded_file($_FILES["upload"]["tmp_name"],$target_path); echo "Stored in: " . 'customerUploads/' . $check_id . ', ' . $c_name . '/' . $_FILES["upload"]["name"]; $insert=mysql_query("INSERT INTO img_up (username,upload,id,target_path,img_date) VALUES ('$manager','$upload','$check_id','$target_path', now()) "); // Where the file is going to be placed $target_path = 'customerUploads/' . $check_id . ', ' . $c_name . '/'; /* Add the original filename to our target path. Result is "uploads/filename.extension" */ $target_path = $target_path . basename( $_FILES['upload']['name']); $target_path = 'customerUploads/' . $check_id . ', ' . $c_name . '/'; $target_path = $target_path . basename( $_FILES['upload']['name']); if (file_exists($target_path)) { echo $_FILES["upload"]["name"] . " already exists. "; } else { move_uploaded_file($_FILES["upload"]["tmp_name"],$target_path); echo "Stored in: " . 'customerUploads/' . $check_id . ', ' . $c_name . '/' . $_FILES["upload"]["name"]; } } } else { echo "There was an error uploading the file, please try again!"; } } header("location: mainupload_complete.php?id=$check_id"); } ?> I have "UPC" setup as a system dsn, type "CodeBaseOdbcStand", and I can connect to it from access. However, I am not having success yet using php. This is a connection to .dbf /.cdx files for data associated with our inventory program. ERROR Code: [Select] PHP Warning: odbc_connect() [<a href='function.odbc-connect'>function.odbc-connect</a>]: SQL error: [Simba][SimbaEngine ODBC Driver][DRM File Library]No such database., SQL state 08004 in SQLConnect in C:\Inetpub\wwwroot\test.php on line 6 CODE Code: [Select] <html> <body> <?php $conn=odbc_connect('UPC','',''); if (!$conn) {exit("Connection Failed: " . $conn);} $sql="SELECT * FROM ARUPC"; $rs=odbc_exec($conn,$sql); if (!$rs) {exit("Error in SQL");} echo "<table><tr>"; echo "<th>UPC</th>"; echo "<th>ITEM</th></tr>"; while (odbc_fetch_row($rs)) { $UPC=odbc_result($rs,"UPC"); $ITEM=odbc_result($rs,"ITEM"); echo "<tr><td>$UPC</td>"; echo "<td>$ITEM</td></tr>"; } odbc_close($conn); echo "</table>"; ?> </body> </html> I am having trouble, because I am trying to enter a string, such as this into a database field: $string = "There's trouble ahead because they're silly."; Ofcourse, MySQL craps out because of the apostrophes. So I did this: $string = mysql_real_escape_string($string); This is entered into the database, however it is entered as this: "There\'s trouble ahead because they\'re silly." I was wondering how I can enter apostrophes, without entering a backslash, because now when I pull the text from the DB and display it on a page, I get a backslash in front of all apostrophes. HELP! Please! Is the only way around this to add slashes, then use stripslashes() when displaying text?? Hi Have an issue with a script that connects to multiple Access databases to extract data. There is one master database and then numerous small databases (I take no responsibility for the design!). The master is opened and then the sub databases are looped around, opened process and closed in turn. However after about 20 connections I get the error [ODBC Microsoft Access Driver] Too many client tasks for any new connections. It is related to the number of connections rather than the number of operations on databases (ie, if I comment out one of the pieces of SQL run on each database it makes no difference). I am closing the connection and unsetting the variable that stores the connection. As such there shouldn't be an excess of connections open at any one time. Any suggestions? All the best Keith Hello, I'm new at PHP programming. I'm creating a simple application. I created a form where users can fill in some data. One of the fields is a username. This is a drop down list. This list is build up with data from SQL Server 2005 R2. I created the code in PHPDesigner7. The ODBC connection is working fine in PHPDesigner7. When I try to run it on the localhost I get no connection The code I used : <?php $conn=odbc_connect('MyDataBase','',''); echo "connectie ".$conn; if (!$conn) {exit("Connection Failed: ". $conn);} $sql="SELECT Id, [Name] FROM Names"; $rs=odbc_exec($conn,$sql); $options=""; echo "<br />"; echo "User : "; echo "<select name='QCT_name'>"; while ($row=odbc_fetch_array($rs)) { $id= $row["Id"]; $Controleur = $row["Name"]; echo "<option value='".$row['Id']."'>".$row['Name']."</option>"; } echo "</select>"; echo "<br />"; ?> Has anybody any clue what I'm doing wrong? This topic has been moved to PHP Regex. http://www.phpfreaks.com/forums/index.php?topic=354362.0 can anyone give me an example of how to establish an ODBC connection with PHP. I have a MS ACCESS database i want to open and read from thanks My script forces all names to start with a capital and lower case for the rest. However, some names have a ' or - in them. For example: Rosie O'Donnell or Carrie-Anne Moss With the script, the "D" in O'Donnell is lower case and the "A" in Anne is lower case. What do I need to do to make them capital? Here is what I am using: Code: [Select] <?php echo ucwords(strtolower($row_persons['last_name'])) ?>, <?php echo ucwords(strtolower($row_persons['first_name'])) ?> I am having trouble transferring data from provideX ODBC to MySQL using Navicat. So, I am wanting to write a php script that will write all of the data to a text file, then do a MySQL "DATA LOAD INFILE". I am VERY limited as far as the SQL functions that are available to me with the ODBC connection. The table does not have a special unique identifier other than the item number. This table I am pulling from has a little over 100,000 items. How can I get php to process the full 100,000 rows. I thought about getting it to process 5,000 rows sorted by item #> break > pass the last item to the next page > then process the next batch of 5,000 sorted by item # where the item is greater than the item processed on the last page. ?? Jake I have not tested this code yet. I just kind of threw it together. Code: [Select] <?php set_time_limit(900); ini_set('max_execution_time', '999'); $myFile = "item_master.txt"; unlink($myFile); $fp = fopen("item_master.txt", "w"); require("..\inc/data.inc"); if (!$conn) {exit("Connection Failed: " . $conn);} $sql="SELECT ITEM_NUM, DESCRIPTION_1, DESCRIPTION_2, ITEM_CLASS, ALPHA_SORT, STANDARD_PACK, GL_TABLE, PRIMARY_VND_NUM, VENDOR_ITEM_NUM, ACTIVE, ITEM_PRICE_CLS FROM ic_inventry_mast"; $rs=odbc_exec($conn,$sql); if (!$rs) {exit("Error in SQL");} while (odbc_fetch_row($rs)) { $ITEM_NUM=trim(odbc_result($rs,"ITEM_NUM")); $DESCRIPTION_1=trim(odbc_result($rs,"DESCRIPTION_1")); $DESCRIPTION_2=trim(odbc_result($rs,"DESCRIPTION_2")); $ITEM_CLASS=trim(odbc_result($rs,"ITEM_CLASS")); $ALPHA_SORT=trim(odbc_result($rs,"ALPHA_SORT")); $STANDARD_PACK=trim(odbc_result($rs,"STANDARD_PACK")); $GL_TABLE=trim(odbc_result($rs,"GL_TABLE")); $PRIMARY_VND_NUM=trim(odbc_result($rs,"PRIMARY_VND_NUM")); $VENDOR_ITEM_NUM=trim(odbc_result($rs,"VENDOR_ITEM_NUM")); $ACTIVE=trim(odbc_result($rs,"ACTIVE")); $ITEM_PRICE_CLS=trim(odbc_result($rs,"ITEM_PRICE_CLS")); $ITEM_NUM=str_replace('@','',$ITEM_NUM); $DESCRIPTION_1=str_replace('@','',$DESCRIPTION_1); $DESCRIPTION_2=str_replace('@','',$DESCRIPTION_2); $ITEM_CLASS=str_replace('@','',$ITEM_CLASS); $ALPHA_SORT=str_replace('@','',$ALPHA_SORT); $STANDARD_PACK=str_replace('@','',$STANDARD_PACK); $GL_TABLE=str_replace('@','',$GL_TABLE); $PRIMARY_VND_NUM=str_replace('@','',$PRIMARY_VND_NUM); $VENDOR_ITEM_NUM=str_replace('@','',$VENDOR_ITEM_NUM); $ACTIVE=str_replace('@','',$ACTIVE); $ITEM_PRICE_CLS=str_replace('@','',$ITEM_PRICE_CLS); $row="$ITEM_NUM@$DESCRIPTION_1@$DESCRIPTION_2@$ITEM_CLASS@$ALPHA_SORT@$STANDARD_PACK@$GL_TABLE@$PRIMARY_VND_NUM@$VENDOR_ITEM_NUM@$ACTIVE@$ITEM_PRICE_CLS\r\n"; fwrite($fp, $row); } fclose($fp); ?> Hi
I am having problems running a while loop twice. The first loop runs fine but the 2nd one does not run. Can anyone please advise why this is? My code is below
Thanks in advance.
Jonathan
$sql = "SELECT id, url, time FROM fyi_links";
$res = odbc_exec($con, $sql);
while ($row = odbc_fetch_array($res))
{
print($row['id'].",".$row['url'].",".$row['time']."\n");
}
//Run loop again after some other code
while ($row = odbc_fetch_array($res))
{
print($row['id'].",".$row['url'].",".$row['time']."\n");
}
I need to send emails with single apostrophes in the body of my email function. How do I do that without erroring out in my mail function. Code: [Select] <? $to = "myself@gmail.com"; $subject = "test"; $body='<html><body><font size="1"> We're sending this email.It's been a while</font></body></html>'; $message=$total; $from = "webmaster@example.com"; $headers = 'MIME-Version: 1.0' . "\r\n"; $headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n"; $headers .= 'From: Admin <Admin@test.com>' . "\r\n"; // Send email if(mail($to,$subject,$body,$headers)) { // Inform the user echo "Your email has been sent"; } else { echo "MAIL not sent"; } ?> CAN ANYONE TELL ME WHAT i AM DOING WRONG. I WANT TO RETRIEVE THE VERY FIRST RECORD IN MY DATABASE WHEN THE CODE EXECUTE IT ONLY SHOWS THE LAST RECORD IN THE DATABASE <?PHP $thisMonth = date('M'); $thisDay = date('j'); $eventMonth = array(); $eventDay = array(); $eventTime = array(); $eventName = array(); $eventLocation = array(); $dbMonth=""; $dbDay=""; $i=0; $conn = odbc_connect('eventsDB','',''); $sql= "SELECT month,day, time, event,location FROM Events"; $rs="$conn,$sql"; if (!$conn) { exit("Connection Failed: " . $conn); } $rs=odbc_exec($conn,$sql); if(!$rs) { exit("Error in SQL"); } echo "DATABASE OPEN"; while($i<3) { $dbMonth= odbc_result($rs,"month"); echo $eventMonth[$i]=odbc_result($rs,"month")."\n"; if($dbMonth<>$thisMonth) { odbc_fetch_row($rs); } echo $eventMonth[$i]=odbc_result($rs,"month")."\n"; echo $eventDay[$i]=odbc_result($rs,"day")."\n"; echo $eventTime[$i]=odbc_result($rs,"time")."\n"; echo $eventDay[$i]=odbc_result($rs,"event")."\n"; echo $eventLocation[$i]=odbc_result($rs,"location")."\n"; $i++; odbc_fetch_row($rs); echo $i; } //ends while loop odbc_close($conn); ?> I have this code: Code: [Select] $sql = "SELECT adpat, adptnm, addate, adtime, adddsc, disdt, distm, drname, adptyp, ptprvtyp FROM hma711.zadpatnmf JOIN hma711.addocrmf on adamdr = drno WHERE addate BETWEEN '20111115' AND '20111115' AND adpat BETWEEN '2000000' AND '2999999'"; $execute = odbc_exec($conn, $sql); $num = odbc_num_rows($execute); echo $num; while($row = odbc_fetch_array($execute)){ echo $row['adpat']; } It gets an error on this line: echo $row['adpat']; The error is Notice: Undefined index: adpat in C:\xampp\htdocs\erboard\test.php on line 29 The query works directly on the AS400 I'm pulling from and the $num is being populated with the correct number of records. This is the first time I have ever used php with odbc so I'm not sure what I'm missing. Any help would be greatly appreciated. Thanks! |
|||||||