1

I do not understand what's happening. I have a query in PHP who crashes -with a strange error-. When I copy/paste the exact same request in PHPMyAdmin it works as expected. What am I doing wrong here ?

 SELECT oms_patient.id,
       oms_patient.date,
       oms_patient.date_modif,
       date_modif,
       AES_DECRYPT(nom,"xxxxx") AS "Nom",
       AES_DECRYPT(prenom,"xxxxx") AS "Prénom usuel",
       DATE_FORMAT(ddn, "%d/%m/%Y") AS "Date de naissance",
       villeNaissance AS "Lieu de naissance (ville)",
       CONCAT(oms_departement.libelle,"(",id_departement,")") AS "Lieu de vie",
       CONCAT(oms_pays.libelle,"(",id_pays,")") AS "Pays",
       CONCAT(patientsexe.libelle,"(",id_sexe,")") AS "Sexe",
       CONCAT(patientprofession.libelle,"(",id_profession,")") AS "Profession",
       IF(asthme>0,"Oui","Non") AS "Asthme",
       IF(rhinite>0,"Oui","Non") AS "Rhinite",
       IF(bcpo>0,"Oui","Non") AS "BPCO",
       IF(insuffisanceResp>0,"Oui","Non") AS "Insuffisance respiratoire chronique",
       IF(chirurgieOrl>0,"Oui","Non") AS "Chirurgie ORL du ronflement",
       IF(autreChirurgie>0,"Oui","Non") AS "Autre chirurgie ORL",
       IF(allergies>0,"Oui","Non") AS "Allergies",
       IF(OLD>0,"Oui","Non") AS "OLD",
       IF(hypertensionArterielle>0,"Oui","Non") AS "Hypertension artérielle",
       IF(infarctusMyocarde>0,"Oui","Non") AS "Infarctus du myocarde",
       IF(insuffisanceCoronaire>0,"Oui","Non") AS "Insuffisance coronaire",
       IF(troubleRythme>0,"Oui","Non") AS "Trouble du rythme",
       IF(accidentVasculaireCerebral>0,"Oui","Non") AS "Accident vasculaire cérébral",
       IF(insuffisanceCardiaque>0,"Oui","Non") AS "Insuffisance cardiaque",
       IF(arteriopathie>0,"Oui","Non") AS "Artériopathie",
       IF(tabagismeActuel>0,"Oui","Non") AS "Tabagisme actuel",
       CONCAT(nbPaquetsActuel," ","PA") AS "",
       IF(tabagismeAncien>0,"Oui","Non") AS "Tabagisme ancien",
       CONCAT(nbPaquetsAncien," ","PA") AS "",
       IF(alcool>0,"Oui","Non") AS "Alcool (conso régulière)",
       IF(refluxGastro>0,"Oui","Non") AS "Reflux gastro-oesophagien",
       IF(glaucome>0,"Oui","Non") AS "Glaucome",
       IF(diabete>0,"Oui","Non") AS "Diabète",
       CONCAT(patienttypeDiabete.libelle,"(",id_typeDiabete,")") AS "",
       IF(hypercholesterolemie>0,"Oui","Non") AS "Hypercholestérolémie",
       IF(hypertriglyceridemie>0,"Oui","Non") AS "Hypertriglycéridémie",
       IF(dysthyroidie>0,"Oui","Non") AS "Dysthyroïdie",
       IF(depression>0,"Oui","Non") AS "Dépression",
       IF(sedentarite>0,"Oui","Non") AS "Sédentarité",
       IF(syndromeDApneesSommeil>0,"Oui","Non") AS "SAS",
       IF(obesite>0,"Oui","Non") AS "Obésité",
       IF(dysmorphieFaciale>0,"Oui","Non") AS "Dysmorphie faciale",
       TextObservations AS "",
       id_user
FROM oms_patient
LEFT JOIN oms_departement ON oms_departement.id = id_departement
LEFT JOIN oms_pays ON oms_pays.id = id_pays
LEFT JOIN patientsexe ON patientsexe.id = id_sexe
LEFT JOIN patientprofession ON patientprofession.id = id_profession
LEFT JOIN patienttypeDiabete ON patienttypeDiabete.id = id_typeDiabete
WHERE oms_patient.id=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 'small">(conso régulière)", IF(refluxGastro>0,"Oui","Non") as "Reflux ga' at line 1

"near 'small" <-- where is small o_O

The PHP code isn't really relevant cause you won't see a lot.

  $db = mysql_connect();
    mysql_select_db();//TODO SWITCH TO PDO
    mysql_query("SET NAMES UTF8");


    $fields = $form->getFields($form);
    $settingsForm = $form->getSettings();
    $sql = 'SELECT oms_patient.id,oms_patient.date,oms_patient.date_modif,';
    foreach ($fields as $field) {
        if (!$field->isMultiSelect()) {
            $field->select_full(&$sql, 'oms_patient', null);
        }
    }
    if (isset($settingsForm['linkTo'])) {
        $idLinkTo = 'id_' . str_replace('oms_', '', $settingsForm['linkTo']);
        $sql .= $idLinkTo;
    }

    $sql.=' FROM oms_patient';
    foreach ($fields as $field) {
        if (!$field->isMultiSelect() && $field->getTable('oms_patient')) {
            $sql .=' LEFT JOIN ' . $field->getTable('oms_patient') . ' ON ' . $field->getTable('oms_patient') . '.id = '.$field->getFieldName().' ';
        }
    }
    $sql.=' where oms_patient.id=' . $this->m_settings['e'];

    $result = mysql_query($sql) or die('Erreur SQL !<br>' . $sql . '<br>' . mysql_error());
    $data = mysql_fetch_assoc($result);

var_dump of $sql

string(2663) "SELECT oms_patient.id,oms_patient.date,oms_patient.date_modif,date_modif,AES_DECRYPT(nom,"xxxxx") as "Nom",AES_DECRYPT("prenom","xxxxx") as "Prénom usuel",DATE_FORMAT(ddn, "%d/%m/%Y") as "Date de naissance",villeNaissance as "Lieu de naissance (ville)",CONCAT(oms_departement.libelle,"(",id_departement,")") as "Lieu de vie",CONCAT(oms_pays.libelle,"(",id_pays,")") as "Pays",CONCAT(patientsexe.libelle,"(",id_sexe,")") as "Sexe",CONCAT(patientprofession.libelle,"(",id_profession,")") as "Profession", IF"...

can't go further to see what is in the output after the "..." <-- if you have an idea

8
  • 2
    you need to show the php version, you must have a missed quotation here or there Commented May 27, 2014 at 14:58
  • I'm not sure it will help you but here it's Commented May 27, 2014 at 15:02
  • What's the result of var_dump($sql); before your $result-statement? Commented May 27, 2014 at 15:03
  • 2
    TODO SWITCH TO PDO shouldn't be a comment but it should always be done :) Commented May 27, 2014 at 15:04
  • Are you sure you're testing the right file? Put an exit statement to see if it's the one. Commented May 27, 2014 at 15:10

1 Answer 1

3

Your problem is most probably that in the SELECT fields, you are having HTML tags being injected.

I don't know exactly what this function does: $field->select_full(&$sql, 'oms_patient', null); , but it is most probably appending strings that contain HTML tags.

You will see this clearly if you do var_dump($sql); and then once you see it in your browser, do right-click->view Source. In the source you will see HTML tags in between your query.

Fix that and you will fix your problem.

Sign up to request clarification or add additional context in comments.

3 Comments

Totaly right ! I feel ashame.... version for the right syntax to use near '<span class="small">(conso régulière)",
So it's the < in <span causing havoc then.
It's not the < that's causing you problems. It's the first quote. The error message clearly states near 'small">. Usually the character preceding the error is the problem. If it was the < then the error would say near span class.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.