Huvudinnehåll
Dataprogrammering
Course: Dataprogrammering > Enhet 2
Lektion 4: Ändra databaser med SQLGör din SQL säkrare
SQL kan vara både fantastiskt men också farligt. Om du använder SQL för att få tillgång till en databas för en app som används av hundratals eller tusentals eller till och med miljontals användare, måste du vara försiktig - eftersom du oavsiktligt kan skada eller radera all datan. Det finns lyckligtvis olika tekniker du kan använda för att göra din SQL säkrare.
Undvika felaktiga uppdateringar / raderingar
Innan du kör en
UPDATE, så kör en SELECT med samma WHERE för att se till att du uppdaterar rätt kolumn och rad.Till exempel, innan du kör:
UPDATE users SET deleted = true WHERE id = 1;Kan du köra:
SELECT id, deleted FROM users WHERE id = 1;När du väl har bestämt dig för att köra uppdateringen, kan du använda LIMIT-operatören för att se till att du inte av misstag uppdaterar för många rader:
UPDATE users SET deleted = true WHERE id = 1 LIMIT 1;Eller om du raderar:
DELETE users WHERE id = 1 LIMIT 1;Använda transaktioner
När vi kör ett SQL-kommando som ändrar vår databas på något sätt, startar vad som kallas en "transaktion". En transaktion är en sekvens av operationer som behandlas som en enda logisk arbetsdel (som en banktransaktion), och i databasvärlden måste en transaktion följa "ACID" -principerna för att säkerställa att de behandlas på ett tillförlitligt sätt.
När vi kör ett kommando som
CREATE, UPDATE, INSERT eller DELETE startar vi automatiskt en transaktion. Men om vi vill kan vi också paketera flera kommandon i en större transaktion. Vi kanske bara vill att en viss UPDATE ska gå igenom om en annan UPDATE också går igenom. Därför vill vi paketera båda i samma transaktion.I det fallet kan vi paketera in kommandona i
BEGIN TRANSACTION och COMMIT:BEGIN TRANSACTION;
UPDATE people SET husband = "Winston" WHERE user_id = 1;
UPDATE people SET wife = "Winnefer" WHERE user_id = 2;
COMMIT;Om databasen inte kan utfärda båda dessa
UPDATE -kommandon av någon anledning, kommer den att rulla tillbaka transaktionen och lämna databasen som den var när transaktionen ifråga startade.Vi använder även transaktioner när vi vill se till att alla våra kommandon opererar med samma vy på data - när vi vill se till att inga andra transaktioner "rör" samma data medan kommando sekvensen körs. När du tittar på en sekvens av kommandon som du vill köra, så fråga dig själv vad som skulle hända om en annan användare utfärdade kommandon samtidigt. Kan dina data hamna i ett konstigt tillstånd? I så fall borde du köra dem i en transaktion.
Följande kommandon skapar t.ex. en rad som anger att en användare har fått ett märke och sedan uppdateras användarens senaste aktivitet för att beskriva det:
INSERT INTO user_badges VALUES (1, "SQL Master", "4pm");
UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;Samtidigt kanske en annan användare eller process tilldelar användaren ett andra märke:
INSERT INTO user_badges VALUES (1, "Great Listener", "4:05pm");
UPDATE user SET recent_activity = "Earned Great Listener badge" WHERE id = 1;Dessa kommandon kan nu faktiskt köras i följande ordning:
INSERT INTO user_badges VALUES (1, "SQL Master");
INSERT INTO user_badges VALUES (1, "Great Listener");
UPDATE user SET recent_activity = "Earned Great Listener badge" WHERE id = 1;
UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;Deras senaste aktivitet skulle nu vara "Earned SQL Master badge" trots att den senast inlagda badgen var "Great listener". Det är inte hela världen, men det är förmodligen inte vad vi förväntade oss.
I stället kunde vi kört dem i en transaktion för att garantera att inga andra transaktioner händer i under tiden:
BEGIN TRANSACTION;
INSERT INTO user_badges VALUES (1, "SQL Master");
UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;
COMMIT;Göra säkerhetskopior
Du bör definitivt följa alla dessa tips, men det händer ibland att det blir fel ändå. Därför gör de flesta företag säkerhetskopior av sina databaser - per timme, dagligen eller veckovis, beroende på databasens storlek och ledigt utrymme. När något dåligt händer kan de sen importera data från den gamla databasen för de tabeller som skadades eller förlorades. Datan kan bli lite gammal, men gammal data är ofta bättre än ingen data alls.
Replikering
Ett relaterat tillvägagångssätt är replikering - att alltid lagra flera kopior av databaserna på olika ställen. Om en viss kopia av databasen av någon anledning inte är tillgänglig (som blixtnedslag i byggnaden den är i, som faktiskt har hänt mig!), Kan frågan skickas till en annan kopia av databasen som förhoppningsvis fortfarande är tillgänglig. Om uppgifterna är mycket viktiga bör de antagligen replikeras för att säkerställa tillgänglighet. Om en läkare exempelvis försöker ta upp en lista över patientens allergier för att bestämma hur man behandlar dem i en nödsituation, kan de inte vänta på att ingenjörer ska få data ut ur en säkerhetskopia, de behöver det omedelbart.
Det är dock mycket mer ansträngning att replikera databaser och det betyder ofta sämre (långsammare) prestanda eftersom skrivoperationer måste utföras av dem alla, så företag måste bestämma om fördelarna med replikering är värda kostnaderna och undersöka det bästa sättet att konfigurera det i sin miljö.
Bevilja privilegier
Många databassystem har användare och privilegier inbyggda i sig, eftersom de lagras på en server och används av flera användare. Det finns inget användarbegrepp i SQL-skript på Khan-Academy eftersom SQLite vanligtvis används i ett användarscenario och därför kan du skriva till den så länge du har tillgång till den enhet som den lagras på.
Men om du använder ett databassystem på en delad server en dag bör du se till att du konfigurerar användare och behörigheter korrekt från början. Som regel bör det bara finnas några användare som har fullständig access till databasen (som backend ingenjörer), eftersom det kan vara så farligt.
Till exempel, såhär kan vi ge full access till en viss användare:
GRANT FULL ON TABLE users TO super_admin;Och så här kan vi ge endast SELECT-åtkomst till en annan användare:
GRANT SELECT ON TABLE users TO analyzing_user;I ett stort företag vill du ofta inte ens ge
SELECT åtkomst till de flesta användare, eftersom det kan finnas privata data i en tabell, som en användares e-postadress eller namn. Många företag har anonymiserade versioner av sina databaser som de kan fråga på utan att oroa sig för tillgång till privat information.Bonus: Läs den här kända XKCD comic om säkrare SQL (plus denna förklaring) .
Vill du gå med i konversationen?
Inga inlägg än.