Nested repeat region and mysql error

[antonio]

Hi all,
I created a nested repeat region using the ADDT.
It works great until the script encounters a a field containing a quote (').
Here is a sample list:

Code: Select all

ABRUZZO
 - city1
 - city2
 - city3
BASILICATA
 - city4
 - city5
 - city6
VALLE D'AOSTA
ou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'aosta' ORDER BY PROV ASC, DENOMINAZIONE ASC' at line 1

The only solution I've found is to change the standard quote to something "similar": `

It's not elegant, but it works.
So, what went wrong in the script?

Here the portion of code related to the nested repaet region (I hope you can help me to sort this out):

Code: Select all

 <?php
  if ($totalRows_rsreg>0) {
    $nested_query_rsnome = str_replace("123456789", $row_rsreg['REGIONE'], $query_rsnome);
    mysql_select_db($database_conn_nardi);
    $rsnome = mysql_query($nested_query_rsnome, $conn_nardi) or die(mysql_error());
    $row_rsnome = mysql_fetch_assoc($rsnome);
    $totalRows_rsnome = mysql_num_rows($rsnome);
    $nested_sw = false;
    if (isset($row_rsnome) && is_array($row_rsnome)) {
      do { //Nested repeat
?>

It seems something related to the quote not being stripped.
But I'm not good programmer and I can't change this code to make it work.

Any suggestion will be really appreciated.

TIA

tony

[Fred]

Tony,
try this

Code: Select all

$row_rsnome = mysql_real_escape_string(mysql_fetch_assoc($rsnome));

Might give you an error.
The idea is to escape the characters before you submit the query to the database for the repeat.

that is to have escaped values in the repeat section here

Code: Select all

    if (isset($row_rsnome) && is_array($row_rsnome)) {
      do { //Nested repeat

[antonio]

Hi Fred,
Thanks for your reply.
But it doesn't seem to work: I get the same mysql error.
This is the code I tried:

Code: Select all

  <?php
  if ($totalRows_rsreg>0) {
    $nested_query_rsnome = str_replace("123456789", $row_rsreg['REGIONE'], $query_rsnome);
    mysql_select_db($database_conn_nardi);
    $rsnome = mysql_query($nested_query_rsnome, $conn_nardi) or die(mysql_error());
    $row_rsnome = mysql_real_escape_string(mysql_fetch_assoc($rsnome));
    $totalRows_rsnome = mysql_num_rows($rsnome);
    $nested_sw = false;
    if (isset($row_rsnome) && is_array($row_rsnome)) {
      do { //Nested repeat
?>

The only solution I have found at this moment is to change all quotes symbol in the master field to something similar (I used this quote: `).
But I think I need to solve this problem anyway.

Do this piece of code (introduced by the recordset) matters?:

Code: Select all

<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

TIA
tony

[Fred]

Here is some info in that code
http://stackoverflow.com/questions/4458 ... g-function

it basically avoid those sort of issues by escaping the data before it is inserted into the database.
Is the data inserted by the user initially?

If so check for and replace with the ' with '’' and see what happens.