vistais Posted July 15, 2008 Share Posted July 15, 2008 I was wondering if anyone knew how to escape single and double quotes within one SQL statement. I can escape one or the other, but not both at the same time. Ex: $SQL = "INSERT INTO open_orders VALUES('', '".$row[0]."', '".$row[1]."', '".$row[2]."', '".$row[3]."', '".$row[4]."', '".$row[5]."', '".$row[6]."', '".$row[7]."', '".$row[8]."', '".$row[9]."', '".$row[10]."', '".$row[11]."', '".$row[12]."', '".$row[13]."', '".$row[14]."', '".$row[15]."', '".$row[16]."', '".$row[17]."', '".$row[18]."', '".$row[19]."', '".$row[20]."' )"; where any one of the rows contain something like ' 12" x 6' '. How would you escape these without escaping the string delimiter (')? Link to comment https://forums.phpfreaks.com/topic/114758-escaping-single-and-double-quotes-in-one-sql-statement/ Share on other sites More sharing options...
fenway Posted July 15, 2008 Share Posted July 15, 2008 That's what mysql_real_escape_string() is for. Link to comment https://forums.phpfreaks.com/topic/114758-escaping-single-and-double-quotes-in-one-sql-statement/#findComment-590517 Share on other sites More sharing options...
vistais Posted July 15, 2008 Author Share Posted July 15, 2008 I did finally end up using mysql_real_escape_string() but I had to escape each value in the array individually. It works, but seems like there's probably a better way to do it. My new code: $r0 = mysql_real_escape_string($row[0]); $r1 = mysql_real_escape_string($row[1]); $r2 = mysql_real_escape_string($row[2]); $r3 = mysql_real_escape_string($row[3]); $r4 = mysql_real_escape_string($row[4]); $r5 = mysql_real_escape_string($row[5]); $r6 = mysql_real_escape_string($row[6]); $r7 = mysql_real_escape_string($row[7]); $r8 = mysql_real_escape_string($row[8]); $r9 = mysql_real_escape_string($row[9]); $r10 = mysql_real_escape_string($row[10]); $r11 = mysql_real_escape_string($row[11]); $r12 = mysql_real_escape_string($row[12]); $r13 = mysql_real_escape_string($row[13]); $r14 = mysql_real_escape_string($row[14]); $r15 = mysql_real_escape_string($row[15]); $r16 = mysql_real_escape_string($row[16]); $r17 = mysql_real_escape_string($row[17]); $r18 = mysql_real_escape_string($row[18]); $r19 = mysql_real_escape_string($row[19]); $r20 = mysql_real_escape_string($row[20]); //inserted data into the columns of the new table called 'display' $SQL = "INSERT INTO open_orders VALUES (\"\", \"$r0\", \"$r1\", \"$r2\", \"$r3\", \"$r4\", \"$r5\", \"$r6\", \"$r7\", \"$r8\", \"$r9\", \"$r10\", \"$r11\", \"$r12\", \"$r13\", \"$r14\", \"$r15\", \"$r16\", \"$r17\", \"$r18\", \"$r19\", \"$r20\" )"; Link to comment https://forums.phpfreaks.com/topic/114758-escaping-single-and-double-quotes-in-one-sql-statement/#findComment-590547 Share on other sites More sharing options...
fenway Posted July 15, 2008 Share Posted July 15, 2008 Yikes... first, if you use single quotes around your DB strings, you won't have to escape your double-quotes. Second, a for() loop is in order. Link to comment https://forums.phpfreaks.com/topic/114758-escaping-single-and-double-quotes-in-one-sql-statement/#findComment-590617 Share on other sites More sharing options...
vistais Posted July 15, 2008 Author Share Posted July 15, 2008 Yea, it's ugly.... i know, but it worked. We recently rewrote it and cleaned it up to this: $i = 1; foreach($data as $raw) { $row = array(); for($j = 0; $j < count($raw); $j++) { $row[$j] = addslashes($raw[$j]); } { $SQL = "INSERT INTO open_orders VALUES(NULL, '$row[0]', '$row[1]', '$row[2]', '$row[3]', '$row[4]', '$row[5]', '$row[6]', '$row[7]', '$row[8]', '$row[9]', '$row[10]', '$row[11]', '$row[12]', '$row[13]', '$row[14]', '$row[15]', '$row[16]', '$row[17]', '$row[18]', '$row[19]', '$row[20]' )"; $result = mysql_query($SQL) or die("Error on record id $i <BR>". $SQL."<BR>".mysql_error()); if(result) $i++; -------------------------------- MUCH BETTER! Link to comment https://forums.phpfreaks.com/topic/114758-escaping-single-and-double-quotes-in-one-sql-statement/#findComment-590636 Share on other sites More sharing options...
fenway Posted July 15, 2008 Share Posted July 15, 2008 Why stop there? A simple $SQL = "INSERT INTO open_orders VALUES(NULL, '" . implode( "','", $row ) . "')"; Will save you a lot of trouble. Link to comment https://forums.phpfreaks.com/topic/114758-escaping-single-and-double-quotes-in-one-sql-statement/#findComment-590655 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.