SQL database stopt met vastleggen records na 128 records

[Frankster]

Ik weet het: na het lezen van de topictitel is direct duidelijk dat ik een complete SQL noob ben. Ik heb een programmaatje (laten) ontwikkelen dat antwoorden van mensen op een test vastlegt in een SQL databse. Nu zie ik geen nieuwe records meer verschijnen na record 128. Het lijkt me dat er dus iets niet goed staat ingesteld. Ik kan met phpmyadmin bij de database, maar heb geen enkel idee wat ik daar moet veranderen (het lijkt me dat ik iets aan de veldinstellingen moet veranderen).

Graag jullie tips! En nee: de programmeur om hulp vragen is helaas geen optie...

[stfn345]

Wellicht het veldtype dat unique is op tinyint(1) gezet en dat daardoor iets misgaat?

[ Voor 21% gewijzigd door stfn345 op 06-11-2010 10:45 ]

[Frankster]

Dit zijn de velden met hun bijzonderheden:

Tabel antwoorden:
id int(11) auto_increment
vraag varchar(255)
antwoord varchar(255)
testid int(11)

Tabel test:
id int(11) auto_increment
ip varchar(50)
tijd datetime
oplossingen int(9)

[Staatslot]

Wat gebeurt er als je handmatig een record toevoegt na het 128e record? Dan kun je misschien uitsluiten dat het niet aan de database ligt maar aan de gebruikte code..?

[cariolive23]

En wat is de foutmelding wanneer je het volgende record aanmaakt? Zonder foutmelding ga je geen oplossing vinden.

[Frankster]

Als ik in tabel 'test' een rij toevoeg krijg ik de volgende foutmelding:

2 rij(en) toegevoegd.
Toegevoegd rij nummer: 131
Warning: #1264 Out of range value adjusted for column 'tijd' at row 2
Warning: #1366 Incorrect integer value: '' for column 'oplossingen' at row 2

Gebruikte query:
INSERT INTO `XXXXXDATABASENAAMXXX`.`test` (`id`, `ip`, `tijd`, `oplossingen`) VALUES ('129', '123.123.123.123', '2010-11-06 11:26:00', '12'), (NULL, '', '', '');

[ Voor 25% gewijzigd door Frankster op 06-11-2010 11:54 ]

[Trucker Her]

Deze query is automatisch gemaakt door phpmyadmin?
Probeer dit dan gewoon eens?
INSERT INTO `XXXXXDATABASENAAMXXX`.`test` (`ip`, `tijd`, `oplossingen`) VALUES ( '123.123.123.123', '2010-11-06 11:26:00', '8')

[ Voor 3% gewijzigd door Trucker Her op 06-11-2010 11:56 ]

[keejoz]

Waarom geen NOW() etc?

[Frankster]

De query:
INSERT INTO `XXXXXDATABASENAAMXXX`.`test` (`ip`, `tijd`, `oplossingen`) VALUES ( '123.123.123.123', '2010-11-06 11:26:00', '8')

Doet het prima! En nu komen er records boven de 128 uit. Ik heb dus het vermoeden dat er gewoon geen mensen de test hebben gedaan...

[Trucker Her]

Probeer: INSERT INTO `XXXXXDATABASENAAMXXX`.`test` (`ip`, `tijd`, `oplossingen`) VALUES ( '123.123.123.123', '2010-11-06 11:26:00', '12') eens dan? Toch even voor de zekerheid. En ik had het laatste stukje weg gehaald. Die probeert volgens mij een tweede record erin te zetten met lege waardes, dat kan dus bij primary index.

[Frankster]

Trucker Her schreef op zaterdag 06 november 2010 @ 11:57:
Probeer: INSERT INTO `XXXXXDATABASENAAMXXX`.`test` (`ip`, `tijd`, `oplossingen`) VALUES ( '123.123.123.123', '2010-11-06 11:26:00', '12') eens dan? Toch even voor de zekerheid. En ik had het laatste stukje weg gehaald. Die probeert volgens mij een tweede record erin te zetten met lege waardes, dat kan dus bij primary index.

Die doet het nu prima!

[Trucker Her]

Mooi. Dus je probleem is zo opgelost? En je hoeft de id niet zelf erin te zetten met de query. Komt omdat ie auto-increment is. Dus die ga bij elke record toch 1 omhoog

[Frankster]

Yep! Is opgelost! Dank voor alle hulp!

[cariolive23]

Hou ook in de gaten dat een lege string '' géén datum met tijd is en dat een lege string '' evenmin een integer is.

Wanneer je default waardes gebruikt zoals bv. een auto_increment, mag je deze
1) in het geheel weglaten (dus de kolomnaam)
2) NULL gebruiken bij de values
3) DEFAULT gebruiken bij de values

DEFAULT gebruiken heeft mijn voorkeur, dan is het ook later voor een ander duidelijk wat de bedoeling is zonder dat men het datamodel hoeft te kennen.

[NMe]

Frankster schreef op zaterdag 06 november 2010 @ 11:27:
Gebruikte query:
INSERT INTO `XXXXXDATABASENAAMXXX`.`test` (`id`, `ip`, `tijd`, `oplossingen`) VALUES ('129', '123.123.123.123', '2010-11-06 11:26:00', '12'), (NULL, '', '', '');

Je weet dat je daar twee inserts probeert te doen, één met echte waardes en één compleet nietszeggend record met lege waardes die niet eens ondersteund worden door de velden waarin je ze wil inserten?

[Remus]

Frankster schreef op zaterdag 06 november 2010 @ 11:27:
Als ik in tabel 'test' een rij toevoeg krijg ik de volgende foutmelding:

2 rij(en) toegevoegd.
Toegevoegd rij nummer: 131
Warning: #1264 Out of range value adjusted for column 'tijd' at row 2
Warning: #1366 Incorrect integer value: '' for column 'oplossingen' at row 2

Gebruikte query:
INSERT INTO `XXXXXDATABASENAAMXXX`.`test` (`id`, `ip`, `tijd`, `oplossingen`) VALUES ('129', '123.123.123.123', '2010-11-06 11:26:00', '12'), (NULL, '', '', '');

Zoals andere al aangeven voeg je hier twee rijen toe, niet een. Doordat die tweede ws niet toegestaan is, faalt de eerste ook. Wat me wel opvalt is dat je quotes om je getallen hebt staan, en dus strings gebruikt, waarom gebruik je strings voor getallen? Nu is het misschien zo dat SQL implementaties die moeten omzetten naar getallen, maar je kan het net zo goed beter niet doen. Daarmee geef je voor iemand die je query leest ook duidelijk aan dat het betreffende veld geen string is.

[NMe]

offtopic:
Ik gok dat hij in phpMyAdmin alle velden ingevuld heeft en daarbij ok geklikt heeft in één van de velden van het tweede record dat je eventueel kan toevoegen. Dan krijg je namelijk standaard dit gedrag, inclusief quotes.

[cariolive23]

Remus schreef op zaterdag 06 november 2010 @ 15:32:
Wat me wel opvalt is dat je quotes om je getallen hebt staan, en dus strings gebruikt, waarom gebruik je strings voor getallen?

Dat er quotes om staan, wil nog niet zeggen dat het dan een string betreft. Of wil je soms beweren dat een DATE ook een string is? Een DATE is een DATE en eist ook quotes. En zo zijn er nog vele andere datatypes waar dit ook voor geldt.

Verder is het ook nog zo dat wanneer je bv. mysql_real_escape_string() gebruikt om SQL injection tegen te gaan, je verplicht bent om quotes te gebruiken. Zonder quotes is het nog steeds mogelijk om SQL injection toe te passen:

PHP:

<?php
$var = "1 OR true";
$query = "SELECT * FROM tabel WHERE id = ".mysql_real_escape_string($var);
echo $query;
?>

En ziedaar, een fijn gehackte query die alle data ophaalt i.p.v. slechts de records met id=1

Mét quotes gaat het wél goed:

PHP:

<?php
$var = "1 OR true";
$query = "SELECT * FROM tabel WHERE id = '".mysql_real_escape_string($var)."'";
echo $query;
?>

Nu zal de query keihard mislukken, er is geen enkel id aanwezig met de waarde '1 OR true'. (aangenomen dat het id een integer is)

Nu is het misschien zo dat SQL implementaties die moeten omzetten naar getallen, maar je kan het net zo goed beter niet doen. Daarmee geef je voor iemand die je query leest ook duidelijk aan dat het betreffende veld geen string is.

Databases zijn slimmer dan dat en een programmeur zal toch iets tegen SQL injection moeten doen en dus toch al moeten nadenken. Liever een paar quotes teveel dan te weinig, dit kan het verschil maken tussen een veilige applicatie en een gehackte database.

[doeternietoe]

cariolive23 schreef op zaterdag 06 november 2010 @ 15:42:

PHP:

<?php
$var = "1 OR true";
$query = "SELECT * FROM tabel WHERE id = ".mysql_real_escape_string($var);
echo $query;
?>

En ziedaar, een fijn gehackte query die alle data ophaalt i.p.v. slechts de records met id=1

Mét quotes gaat het wél goed:

PHP:

<?php
$var = "1 OR true";
$query = "SELECT * FROM tabel WHERE id = '".mysql_real_escape_string($var)."'";
echo $query;
?>

Nu zal de query keihard mislukken, er is geen enkel id aanwezig met de waarde '1 OR true'. (aangenomen dat het id een integer is)

Leuk bedacht, als het niet zo was dat de functie mysql_real_escape_string() helemaal niet bedoeld is om SQL injections met integers te voorkomen. Zoals de naam het al zegt moet je deze functie gebruiken voor strings. Voor integers zou ik eerder denken aan functies als is_int() of intval(). Als vast staat dat een ingevoerde waarde werkelijk een integer is, mag deze echt zonder quotes in de query.

Indien beschikbaar zou het gebruik van prepared statements overigens het einde van de discussie betekenen, omdat je dan toch het meeste werk uit handen wordt genomen.

[ Voor 7% gewijzigd door doeternietoe op 06-11-2010 15:55 ]

[cariolive23]

doeternietoe schreef op zaterdag 06 november 2010 @ 15:53:
[...]

Leuk bedacht, als het niet zo was dat de functie mysql_real_escape_string() helemaal niet bedoeld is om SQL injections met integers te voorkomen.

In 9 van de 10 tutorials over SQL injection i.c.m. PHP en MySQL wordt dit echter wel als oplossing aangedragen en 9 van de 10 stukken code gebruiken het ook op deze wijze. Dan is het wel zo handig om het goed te doen.

Zoals de naam het al zegt moet je deze functie gebruiken voor strings.

Mee eens, alleen jammer dat PHP de hele wereld aanziet voor een string

Voor integers zou ik eerder denken aan functies als is_int() of intval(). Als vast staat dat een ingevoerde waarde werkelijk een integer is, mag deze echt zonder quotes in de query.

Helemaal waar, je voelt er alleen helemaal niets van door er quotes om te zetten. Dat is echt niet fout en het kan problemen voorkomen.

Indien beschikbaar zou het gebruik van prepared statements overigens het einde van de discussie betekenen, omdat je dan toch het meeste werk uit handen wordt genomen.

MySQL heeft een beroerde ondersteuning voor prepared statements en alleen wanneer je de mysqli-functies van PHP gebruikt (of PDO die mysqli gebruikt) heb je de beschikking over prepared statements. Dat levert dan wel weer extra werk op, wat in feite onnodig is.

PHP heeft dit voor PostgreSQL vele malen eenvoudiger geïmplementeerd, pg_query_params() neemt al het werk voor jou uit handen en je bent direct van het "gezeur" af met quotes:

PHP:

<?php 
$var = "1 OR true"; 
$query = "SELECT * FROM tabel WHERE id = $1"; // $1 is een placeholder, quotes heb je nooit nodig voor placeholders
$result = pg_query_params($query, array($var)); // zal keihard falen, $var is geen integer 
?>

[doeternietoe]

cariolive23 schreef op zaterdag 06 november 2010 @ 16:45:
[...]

In 9 van de 10 tutorials over SQL injection i.c.m. PHP en MySQL wordt dit echter wel als oplossing aangedragen en 9 van de 10 stukken code gebruiken het ook op deze wijze. Dan is het wel zo handig om het goed te doen.

9 van de 10 tutorials zouden het ook fout(of minder goed) kunnen hebben.

[...]

Mee eens, alleen jammer dat PHP de hele wereld aanziet voor een string

PHP heeft wat quirks.

Het zou op het gebruik van type casting of intval() geen invloed mogen hebben, dacht ik.

[...]

Helemaal waar, je voelt er alleen helemaal niets van door er quotes om te zetten. Dat is echt niet fout en het kan problemen voorkomen.

Bij SELECT queries niet nee, je krijgt dan gewone een lege resultset. Bij INSERT of UPDATE queries levert het invoeren van een string met niet numerieke gegevens echter een error op die je vrij simpel kunt voorkomen. Ik ben van mening dat je betere code produceert als je bij iedere query goed nadenkt over het type data en de controle dan wanneer je "omdat het kan" gewoon overal maar mysql_real_escape_string() voor gebruikt en overal quotes omheen zet.

[...]

MySQL heeft een beroerde ondersteuning voor prepared statements en alleen wanneer je de mysqli-functies van PHP gebruikt (of PDO die mysqli gebruikt) heb je de beschikking over prepared statements. Dat levert dan wel weer extra werk op, wat in feite onnodig is.

Ik bedoel inderdaad mysqli en PDO. Het gebruik daarvan levert over het algemeen meer regels code op, maar om nu te zeggen dat het veel meer werk is dan met de hand escapen...

[NMe]

cariolive23: prepared statements gaan in PHP ook prima en als je vindt van niet dan zou ik dat graag onderbouwd zien. Verder zijn de mysqli_-functies in PHP juist bedoeld om op termijn de mysql_-functies te vervangen, voor zover ik weet.

[cariolive23]

@NMe: De mysql-functies (dus zonder de i) van PHP hebben helemaal geen ondersteuning voor prepared statements en voor de mysqli-functies (dus met i) en PDO moet je een hoop extra toeren uithalen om de resultaten te verwerken:

The parameter markers must be bound to application variables using mysqli_stmt_bind_param() and/or mysqli_stmt_bind_result() before executing the statement or fetching rows.

Waarom zou je het resultaat van de query nog weer moeten binden? Dat is met bv. Firebird, Oracle of PostgreSQL niet nodig, een resultaat is een resultaat, het preparen gaat om de input, niet om de output.

Daarnaast kun je in MySQL een bestaand prepared statement zonder problemen overschrijven:

If a prepared statement with the given name already exists, it is deallocated implicitly before the new statement is prepared. This means that if the new statement contains an error and cannot be prepared, an error is returned and no statement with the given name exists.

Dit kan weer tot bijzondere bugs leiden.

In vergelijking met diverse andere databases, is de implementatie van prepared statements in MySQL en de ondersteuning daarvan met PHP, verre van ideaal.

[Trucker Her]

Ehm, ja. Leuke discussie. Heeft dit nog maar iets met het topic te maken? Iniedergeval de dingen die ik las zijn al vaker vertelt in dit topic. Maar dat stukje van DEFAULT wist ik nog niet eens. Dank daarvoor.

[NMe]

Trucker Her schreef op zaterdag 06 november 2010 @ 22:13:
Ehm, ja. Leuke discussie. Heeft dit nog maar iets met het topic te maken?

Maakt dat wat uit als het probleem van de topicstarter is opgelost?

@cariolive23: dat iets anders werkt dan jij gewend bent maakt het nog geen brakke implementatie. Jouw bind-argument gaat dus niet op, IMO. Wat betreft het overschrijven van prepared statements geef ik je gelijk, dat is verwarrend. Maar ook dat maakt niet de hele implementatie brak.

Prepared statements i.c.m. mysqli werken prima en doen gewoon wat ze moeten doen.

[Avalaxy]

NMe schreef op zaterdag 06 november 2010 @ 23:28:
[...]

Prepared statements i.c.m. mysqli werken prima en doen gewoon wat ze moeten doen.

Los van de prepared statements en object georiënteerde opzet dient PDO ook nog een ander doel, namelijk de gemakkelijke portability naar andere DBMS'en

Overigens denk ik zelfs dat het op den duur zelfs makkelijker is om gewoon iets te doen van:

PHP:

$query->execute(array('foo', 'bar'));

Dan telkens:

PHP:

$foo = mysql_real_escape_string($foo);
$bar= mysql_real_escape_string($bar);
mysql_query('blabla $foo blabla $bar');

Uiteraard op voorwaarde dat je je queries al geprepared hebt, maar dat zul je wel vaker tegenkomen bij grote(re) applicaties.

[NMe]

Mwah, op kantoor hebben we een eigen databaseclass die een afgeleide class heeft voor elke database-engine die we ooit nodig gehad hebben. Daar roepen we een query als volgt aan:

PHP:

$db->Query("SELECT * FROM tabel WHERE foo = %bar", array('bar' => $bar));

Onder water wordt het netjes uitgewerkt tot een veilige query voor die specifieke DB-engine. Mijn punt is dan ook dat het niet zozeer uitmaakt hoe de standaardfuncties precies werken, aangezien je meestal toch je eigen interface maakt om te zorgen dat je framework niet afhankelijk wordt van de gekozen database.

Voor de T-SQL-implementatie van die class heb ik nog een nifty methode geschreven om te zorgen dat LIMIT-query's alsnog blijven werken, scheelde wat herschrijven van query's.

[Avalaxy]

Netjes

[Matis]

NMe schreef op zondag 07 november 2010 @ 02:19:
Mwah, op kantoor hebben we een eigen databaseclass die een afgeleide class heeft voor elke database-engine die we ooit nodig gehad hebben. Daar roepen we een query als volgt aan:

PHP:

$db->Query("SELECT * FROM tabel WHERE foo = %bar", array('bar' => $bar));

Onder water wordt het netjes uitgewerkt tot een veilige query voor die specifieke DB-engine. Mijn punt is dan ook dat het niet zozeer uitmaakt hoe de standaardfuncties precies werken, aangezien je meestal toch je eigen interface maakt om te zorgen dat je framework niet afhankelijk wordt van de gekozen database.

Voor de T-SQL-implementatie van die class heb ik nog een nifty methode geschreven om te zorgen dat LIMIT-query's alsnog blijven werken, scheelde wat herschrijven van query's.

Hoe weet jouw interface dan welk datatype $bar is/moet zijn? Ik bedoel, bij PDO kun je aangeven wat voor parameter je variabele moet hebben.

[ Voor 4% gewijzigd door Matis op 07-11-2010 16:22 ]

[NMe]

Matis schreef op zondag 07 november 2010 @ 16:22:
[...]

Hoe weet jouw interface dan welk datatype $bar is/moet zijn? Ik bedoel, bij PDO kun je aangeven wat voor parameter je variabele moet hebben.

Gewoon overal quotes omheen zetten, zo uit het hoofd. Types boeien niet in een weak typed taal zoals PHP en wat DB-engines betreft gaat het met quotes eromheen altijd goed, als in: zonder injectieproblemen. Maar dat heeft cariolive23 hierboven al aangegeven.