Contenido principal
Curso: Programación de computadoras > Unidad 3
Lección 4: Modificar bases de datos con SQLHaz tu SQL más seguro
SQL puede ser una cosa hermosa, pero también peligrosa. Si estás usando SQL para acceder a una base de datos de una app que tiene cientos, miles o millones de usuarios, debes tener cuidado, porque accidentalmente podrías dañar o borra todos los datos. Sin embargo, hay varias técnicas que puedes usar para hacer que tu SQL sea más seguro.
Evitar malas actualizaciones o eliminaciones
Antes de emitir un
UPDATE, ejecuta un SELECT con la misma cláusula WHERE para asegurarte de que estás actualizando la columna y el renglón correctos.Por ejemplo, antes de ejecutar:
UPDATE users SET deleted = true WHERE id = 1;Podrías ejecutar:
SELECT id, deleted FROM users WHERE id = 1;Una vez que decidas ejecutar la actualización, puedes usar el operador LIMIT para asegurarte de no actualizar demasiados renglones por accidente:
UPDATE users SET deleted = true WHERE id = 1 LIMIT 1;O si estuvieras eliminando:
DELETE users WHERE id = 1 LIMIT 1;Usar transacciones
Cuando emitimos un comando SQL que cambia nuestra base de datos de alguna manera, empieza lo que se llama una "transacción". Una transacción es una secuencia de operaciones tratadas como una sola pieza lógica de trabajo (como una transacción bancaria), y en el mundo de las bases de datos, una transacción debe cumplir con los principios "ACID" para asegurarse de que las operaciones sean procesadas de manera confiable.
Siempre que emitimos un comando como
CREATE, UPDATE, INSERT, o DELETE, automáticamente estamos iniciando una transacción. Sin embargo, si queremos, también podemos envolver muchos comandos en una transacción más grande. Puede ser que solo queramos que un UPDATE ocurra si es que otro UPDATE también ocurre, de modo que queremos ponerlos a los dos en la misma transacción.En ese caso, podemos rodear esos comandos con
BEGIN TRANSACTION y COMMIT:BEGIN TRANSACTION;
UPDATE people SET husband = "Winston" WHERE user_id = 1;
UPDATE people SET wife = "Winnefer" WHERE user_id = 2;
COMMIT;Si la base de datos no es capaz de emitir esos dos comandos
UPDATE por alguna razón, entonces deshace la transacción y deja la base de datos como estaba cuando empezó.También usamos transacciones cuando queremos estar seguros de que todos nuestros comandos operen en la misma vista de los datos, cuando queremos asegurar que ninguna otra transacción opere en esos mismos datos mientras la secuencia de comandos se está ejecutando. Cuando veas una secuencia de comandos que quieres ejecutar, pregúntate qué pasaría si otro usuario emitiera los mismos comandos al mismo tiempo. ¿Tus datos podrían terminar en un estado extraño? En ese caso, deberías ejecutar una transacción.
Por ejemplo, los siguientes comandos crean una fila al denotar que un usuario ha ganado una medalla y después actualiza la actividad reciente del usuario para describir eso:
INSERT INTO user_badges VALUES (1, "Maestro en SQL", "4pm");
UPDATE user SET recent_activity = "Ganó la medalla de Maestro en SQL" WHERE id = 1;Al mismo tiempo, otro usuario o proceso podría estar otorgándole al usuario una segunda medalla:
INSERT INTO user_badges VALUES (1, "Gran oyente", "4:05pm");
UPDATE user SET recent_activity = "Ganó medalla de Gran oyente" WHERE id = 1;Estos comandos ahora podrían emitirse en este orden:
INSERT INTO user_badges VALUES (1, "Maestro en SQL");
INSERT INTO user_badges VALUES (1, "Gran oyente");
UPDATE user SET recent_activity = "Ganó la medalla de Gran oyente" WHERE id = 1;
UPDATE user SET recent_activity = "Ganó la medalla de Maestro en SQL" WHERE id = 1;Su actividad reciente ahora sería "Ganó la medalla de Maestro en SQL" aunque la medalla insertada más reciente haya sido "Gran oyente". Eso no es el fin del mundo, pero probablemente tampoco sea lo que esperábamos.
En cambio, podríamos ejecutarlos en una transacción, para garantizar que ninguna otra transacción ocurra en medio:
BEGIN TRANSACTION;
INSERT INTO user_badges VALUES (1, "Maestro en SQL");
UPDATE user SET recent_activity = "Ganó la medalla de Maestro en SQL" WHERE id = 1;
COMMIT;Hacer respaldos
Definitivamente, deberías seguir todos esos consejos, pero hay veces que ocurren errores de todas maneras. Por eso, la mayoría de las compañías hacen respaldos de sus bases de datos, cada hora, día o semana, dependiendo del tamaño de la base de datos y del espacio disponible. Cuando algo malo sucede, pueden importar los datos de la base de datos anterior para cualquier tabla que se haya dañado o perdido. Los datos pueden estar un poco desactualizados, pero tener datos desactualizados es mejor que no tener datos.
Replicación
Un enfoque relacionado es la replicación: siempre almacenar varias copias de las bases de datos en diferentes lados. Si por alguna razón una copia en particular de la base de datos no está disponible (como porque le cayó un rayo al edificio en donde está, que de hecho ¡me ha pasado a mí!), entonces la consulta se puede enviar a otra copia de la base de que ojalá esté disponible. Si los datos son muy importantes, entonces probablemente deberían estar replicados, para asegurar su disponibilidad. Por ejemplo, si un médico está tratando de revisar el expediente de las alergias de un paciente para determinar como tratarlas en una situación de emergencia, entonces no puede darse el lujo de esperar a que los ingenieros restauren un respaldo de la base de datos, los necesita inmediatamente.
Sin embargo, require un esfuerzo mucho mayor replicar bases de datos y eso a menudo significa un desempeño más lento ya que las operaciones de escritura tienen que hacerse en todas ellas, así que las empresas deben decidir si los beneficios de la replicación valen los costos, y deben investigar la mejor manera para configurar su entorno.
Otorgar privilegios
Muchas bases de datos tienen usuarios y privilegios incorporados en ellas porque están almacenadas en un servidor y son accesadas por múltiples usuarios. En los scripts de SQL en Khan Academy no existe el concepto de usuario y privilegio porque SQLite típicamente se usa en un escenario de un solo usuario, así que puedes escribir en ella siempre y cuando tengas acceso a la unidad en donde está almacenada.
Pero si algún día usas un sistema de base de datos en un servidor compartido, entonces deberías configurar usuarios y privilegios de manera apropiada desde el principio. Como regla general, solo debería haber unos cuantos usuarios con acceso completo a la base de datos (como los ingenieros del backend), puesto que puede ser peligroso.
Por ejemplo, aquí está cómo podemos darle acceso total a un usuario en particular:
GRANT FULL ON TABLE users TO super_admin;Y aquí está cómo podemos solamente darle acceso a SELECT a un usuario diferente:
GRANT SELECT ON TABLE users TO analyzing_user;En una compañía grande, a menudo ni siquiera quieres darle acceso a
SELECT a la mayoría de los usuarios, porque podría haber datos privados en una tabla, como el correo electrónico del usuario o su nombre. Muchas compañías tienen versiones anonimizadas de sus bases de datos que pueden consultar sin preocuparse acerca del acceso a información privada.Extra: lee este cómic XKCD famoso sobre SQL más seguro (más esta explicación).
¿Quieres unirte a la conversación?
En replicación, ¿De verdad le cayó un rayo al edificio?(16 votos)
No, sería más fácil si borras toda la base de datos con un 'DROP DATABASE',?(3 votos)
¿Como puedo mejorar mi sql un poco mas?(3 votos)
La práctica hace al maestro, entre más practique mejor serás(8 votos)
- ¿En que medida es confiable SQL?(2 votos)
para que sirven las consultas sql(2 votos)
¿como puedes relacionar 2 tablas desde el create table?(2 votos)
Se pueden borrar todas las tablas desde una sola sentencia?(2 votos)- No, sería más fácil si borras toda la base de datos con un 'DROP DATABASE', en dado caso que tengas algunos otros objetos como procedimientos almacenados, o usuarios, etc. Tendrías que borrar las tablas una a una, pero eliminando primero los datos que hay en tablas hijo y eliminar después las tablas padre, por aquello de las reglas de integridad.
Pero como tal no hay una sentencia tipo 'DROP TABLE ALL' que te ayude con eso.(3 votos)
- gracias por la informacion dada(1 voto)
- que hace tan inseguro mi sql?(1 voto)