Keskustelu: Ohjelmointikysymykset: PHP, MySQL, MariaDB ja merkkijono/NULL

Tietokannan firstname-sarakkeen arvo ei saa olla NULL. Oletusarvoa ei ole määritetty. Tyyppi on text.

Tietokannan lastname-sarake saa olla NULL ja oletusarvo on NULL. Tyyppi on text.

Ongelma on se, että jos $_POST['firstname'] on tyhjä merkkijono, tietokantaan yritetään tallentaa arvoa NULL, mutta arvo ei saa olla NULL. Pitäisi tallentua arvo "", mutta niin ei käy vaan tulee virheilmoitus, jonka mukaan firstname ei saa olla NULL.

Jos $_POST['lastname'] on tyhjä merkkijono, kaikki toimii OK.

Miten koodia voisi muokata siten, että tietokantaan tallentuisi tyhjä merkkijono "" sellaisiin sarakkeisiin kuin firstname, jotka eivät saa olla NULL, mutta tyhjä merkkijono tallentuisi NULLina sarakkeisiin kuten lastname, jotka saavat olla NULL?

Kaikki tuntui toimivan OK MySQL-kannalla. Nyt käytössä on MariaDB 10.3.27 ja ongelmat ilmenivät siihen siirtymisen yhteydessä.

// ensin otetaan yhteys tietokantaan ja sitten alkaa tämä koodi...

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $conn, $theDefinedValue = "", $theNotDefinedValue = "")
{
  $theValue = function_exists("mysqli_real_escape_string") ? mysqli_real_escape_string($conn, $theValue) : mysqli_escape_string($conn, $theValue);

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

$updateSQL = sprintf("UPDATE members SET firstname=%s, lastname=%s WHERE id = 1",
GetSQLValueString($_POST['firstname'], "text", $connSivusto)
, GetSQLValueString($_POST['lastname'], "text", $connSivusto)
);

mysqli_select_db($connSivusto, $database_connSivusto);
$Result1 = mysqli_query($connSivusto, $updateSQL) or die(mysqli_error($connSivusto));

Sarakkeisiin tallentuu se arvo, minkä yrität niihin tallentaa. Eli tallenna nullin sijaan tyhjä merkkijono. Osaathan sinä nyt varmasti sen verran noin lyhyttä koodia lukea, että ymmärrät mitä esimerkiksi rivillä 10 tapahtuu?

Jos taas $_POST['lastname'] on tyhjä merkkijono, kaikki toimii OK ja kantaan tallentuu NULL. Tietokannan lastname-sarake saa olla NULL ja oletusarvo on NULL. Tyyppi on text.

Ongelma on kuitenkin tuo firstname, joka ei saa olla NULL.

Kuten The Alchemist koetti sinulle vihjata, koodissasi (yllä rivillä 10) on nimenomaan tarkastus, jossa vaihdat tyhjän merkkijonon tilalle arvon NULL, joten lopputulos on täysin itse aiheutettu.

Voit laittaa funktiolle lisäparametrin $emptyValue = "NULL" ja vaihtaa funktiossa kohtaan "NULL" tämän parametrin ja sitten voit firstname-parametrin kohdalla antaa parametriksi $emptyValue = "''".

Parempi ratkaisu olisi se, että tekisit koko tietokantasysteemin eri tavalla. Vähintäänkin pitäisi erottaa arvon looginen käsittely (tyhjän merkkijonon tunnistus ja muutto NULL-arvoksi joissain tilanteissa) ja SQL-kyselyyn tarvittava käsittely (escape). Jälkimmäisen sijasta voisit valmistella kyselyt funktiolla mysqli_prepare ja antaa parametrit funktiolla mysqli_stmt_bind_param, jolloin escapea ei tarvitse käyttää ollenkaan.

Muuttujien nimissä the-sanan käyttö on aika outo idea.

Miksi funktion ympärillä on function_exists-tarkastus? Funktion olisi syytä olla omassa tiedostossaan, josta sen saisi include_once:lla mukaan.

Sillä ei liene merkitystä, että olen kommentoinut heti GetSQLValueString-funktion alusta seuraavan rivin, joka siinä vielä PHP 5:n aikaan oli käytössä?

// $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

Tietääkseni tuo on ylimääräinen ja myös toimimaton rivi, kun käytössä on PHP 7.4.

Ei ole merkitystä.

Oliko ratkaisussa vielä jotain epäselvää sinulle? Kertaan vielä.

Koodissasi on nyt rivi, jossa tarkastat, onko arvona tyhjä merkkijono, ja vaihdat siihen silloin NULL-sanan:

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

Edellä selitin yhden vaihtoehdon, miten tätä tilannetta voi muuttaa:

$theValue = ($theValue != "") ? "'" . $theValue . "'" : $emptyValue;

Epäselvää on se, miksi vastaava koodi on mysql-komennoilla (ei mysqli) toiminut ongelmitta PHP 5.6:lla ja MySQL-kannalla, mutta ei toimi yhdistelmällä PHP 7.4, MariaDB ja mysqli.

Varmaan tietokannassa on ollut kuitenkin jotain eroa, esim. NULL-arvo on ollut sallittu tai jokin vanha MySQL-versio on muuttanut arvon automaattisesti tyhjäksi merkkijonoksi. Tässä asiassa ei ole merkitystä PHP:n versiolla tai mysql- ja mysqli-laajennosten eroilla.

MySQL on perinteisesti sallinut työntää kantaan mitä tahansa roskaa ja se on sen jälkeen tehnyt omat automagiansa arvoille. On vain ihan hyvä, että Mariakin on viimein siirtynyt ns. strict modeen, joka pakottaa ihmiset myös ajattelemaan hieman.

Tuon moodin voi totta kai myös kääntää pois päältä.