The user wants a web page with SQLite handling users, so I'm planning a Flask application with built-in sqlite3, password hashing via werkzeug, sessions for login state, and basic user roles to cover registration, login, logout, and profile management. I'm sketching the project structure with templates for registration, login, dashboard, profile, password change, and admin pages, plus a stylesheet. I'm deciding on features: hashed passwords, admin/user roles with a user management panel, profile editing, and keeping CSRF protection simple to avoid extra dependencies. Writing app.py setup... I'm also thinking through the SECRET_KEY tradeoff: using an environment variable with a random fallback means sessions get invalidated on restart, which is acceptable for dev but could be annoying with the debug reloader constantly restarting. Still writing database setup... Now I'm drafting the users table schema with unique username/email constraints and a role field. For the schema file, I'm weighing whether to keep it inline as a string in app.py versus a separate schema.sql file -- I'll go with inlining it to keep things simpler for the user to copy. For CSRF protection, I'm writing a token generator that stores a random hex value in the session, exposing it to Jinja templates, and adding a before_request hook that rejects POST requests unless the form token matches the session token using a constant-time comparison. I realize compare_digest can throw a TypeError on non-ASCII strings, so I switch to encoding both tokens to bytes before comparing with hmac.compare_digest. Now I'm writing login_required and admin_required decorators that redirect unauthenticated users to the login page with a flash message, and raise a 403 for non-admins, while being careful about the order of decorator stacking. I'm also setting up the before_request handler to load the current user from the session into g.user, clearing the session if the user record is missing or inactive, and defining regex patterns to validate usernames and email addresses. Now I'm adding a password validator requiring at least 8 characters, plus a letter and a number. Then I'm sketching out the full route structure: index, register, login, logout, dashboard, profile editing, password change, and the admin routes for listing, editing, toggling, and deleting users, possibly adding an admin-create-user route too. I decide to reuse a single `admin_user_form.html` template for both creating and editing users. I'm also writing a helper to prevent open redirects by only allowing relative paths that start with a single slash, then writing the login route logic to look up a user by username or email and verify their password. I'm now thinking about mitigating user enumeration via timing attacks -- if the user doesn't exist, the check_password_hash call gets skipped entirely, leaking information through response timing. I decide to add a dummy hash comparison that always runs even when no user is found, to keep response times consistent. I also note that session.clear() wipes the CSRF token, but a fresh one regenerates on the next render, which helps prevent session fixation. For brute-force protection, I consider mentioning rate limiting (like Flask-Limiter) as a suggested improvement rather than implementing a full attempt-counter table, since that would add too much complexity to this answer. Now I'm building the register route, validating username, email, password strength, and confirmation match before proceeding to create the account... Continuing the registration logic... That race is negligible in practice. I should also fix the form-clearing issue on validation errors by passing back the submitted values so users don't lose their input, using request.form in the template re-render. For dashboard and profile, I'm adding user stats for admins and building a profile route that lets users update their full name and email, validating the email format and updating the database before redirecting with a success flash message. Now I'm adding a change password route that checks the current password hash, confirms the new password matches, and validates it against password rules before updating and flashing success. I'll avoid the walrus operator for clarity and use a plain variable instead. Then I'm drafting an admin users listing route that supports a search query against username, email, and full name using LIKE, though I note that raw `%` and `_` characters in user input would behave as SQL wildcards, which isn't a security issue but could produce unexpected search results. For the create/edit admin user functionality, I realize I can consolidate two separate routes into a single handler using Flask's `defaults` parameter, sharing logic for fetching the user record and handling form submission. Writing user form handler... I need to add validation for the submitted fields, and importantly prevent an admin from accidentally demoting or deactivating their own account while editing themselves -- I should just force the role and active status to remain unchanged in that case rather than silently allowing a dangerous self-edit. The "last admin" concern resolves itself since the current admin can never demote or deactivate themselves, so there's always at least one admin remaining; a race condition between two admins demoting each other simultaneously is an edge case I'll ignore for now. Writing insert/update query logic... I also catch that `form = user or {}` is buggy since sqlite3.Row doesn't support `.get()` — I need `dict(user) if user else {}` instead so the template's form field lookups work correctly, including for the `is_active` checkbox state on re-render versus fresh load from the database. For new user creation, I'm defaulting `is_active` to 1 and `role` to "user" so the form shows sensible defaults on GET requests. Writing the toggle-active route and delete route, with a safeguard preventing admins from deactivating their own account. For deletion, I'll block self-deletion to avoid leaving the system without admins, and skip adding self-deletion to the profile page to keep things simple. I'm also adding error handlers for 403, 404, and 400 that render a shared error template. For logout, I'll make it a POST with CSRF protection to prevent logout CSRF attacks, styling the submit button to look like a link. I'm debating whether to add basic security headers via an after_request hook, but might skip it to keep the code manageable. I want init_db() called at module level rather than just inside the main block, so it works whether run via flask run or python app.py, since the table creation is idempotent. For the debug flag, I should pull it from an environment variable rather than hardcoding True, to avoid leaving debug mode on in production. I'll just keep debug=True with a comment reminding to disable it in production, since simplicity matters more here. Now I'm planning the templates: a base.html with navbar, flash messages, and a content block, plus a clean self-contained CSS file with cards, forms, tables, buttons, and alerts. Writing base template... Finishing the layout, I add a flashed-messages block using Flask's categorized messages and a content block for child templates, then move on to the registration page template with its own card layout. I'm removing the `novalidate` attribute since server-side validation already exists, so browser validation can also kick in. I decide to always pass `request.form` as the form context since it's safely empty on GET requests, then move to the login template which needs an identifier/password field and handling for the `next` redirect parameter. Now I'm sketching out the remaining templates: dashboard with welcome info and admin stats, profile and password-change forms, and the admin user management page with a search form and table showing user details plus edit/activate/delete actions with JS confirmation dialogs. I also note that SQLite's `datetime('now')` stores timestamps in UTC. Writing CSS variables and base styles... Now I'll set up the requirements.txt with Flask, and draft the setup instructions for creating a virtual environment and installing dependencies. I should also note that the first registered user becomes admin, plus add security notes and possible future improvements like rate limiting, email verification, Flask-Login, and production deployment with gunicorn. I might mention offering a Node.js alternative briefly, but it's not essential. Now let me carefully write the complete app.py, handling secret key generation, database setup, and authentication routes with password hashing. Defining the users table schema... Finishing the schema with a last_login column, then confirming that since username and email are declared COLLATE NOCASE, a login query comparing them with `=` against a parameter will correctly use case-insensitive matching. For CSRF protection, I'm setting up a helper that generates and stores a token in the session if one doesn't exist, then exposing it to templates—either by registering it as a Jinja global or via a context processor. I'll go with the Jinja global approach. Then I'm adding a before_request hook that rejects POST requests if the submitted CSRF token doesn't match the session's token using a constant-time comparison, aborting with a 400 error and a Spanish message about an invalid token or expired session. Finally, I'm adding an after_request hook to set security headers like X-Content-Type-Options, X-Frame-Options, and Referrer-Policy on every response. I'm adding error handlers for 400/403/404 that render an error template showing the exception's code, name, and description. I realize there's a potential issue: since csrf_protect runs before load_logged_in_user in the before_request chain, a CSRF-triggered 400 could render the error template before g.user is set, meaning the template's reference to g.user might be undefined -- I need to check how Jinja handles that undefined attribute access on g. Since Jinja's attribute lookup falls back to Undefined rather than raising, `{% if g.user %}` just evaluates falsy, so it's not technically an error -- but I'll reorder to run load_logged_in_user before csrf_protect so the nav state is accurate even on CSRF failures. Now I'm writing regex patterns for validating usernames and emails, plus a password validation function that checks minimum length and requires both a letter and a digit, returning Spanish error messages when requirements aren't met. I should also cap password length at 128 characters to avoid DoS via extremely long inputs, cap email at 254 characters, and truncate full_name to 100 characters. I'm consolidating these into helper functions `validate_username` and `validate_email` that return error strings or None, so I can reuse them across the registration and admin forms while profile editing only needs the email check. I'm writing a safe-redirect helper that confirms a target starts with a single slash and contains no backslashes to prevent open redirects. For the admin dashboard, I'm adding a query that counts total users, active users, and admins using conditional SUM expressions since SQLite treats boolean comparisons as 0/1. The logout route clears the session, flashes a confirmation message, and redirects to the index page. Now I'm working out how to combine the "new user" and "edit user" admin routes using a single view function with two route decorators, where one supplies a default of None for the user_id parameter. I need to verify that Werkzeug's URL building correctly resolves `url_for('admin_user_form')` to the new-user path and `url_for('admin_user_form', user_id=5)` to the edit path given this defaults setup. Still tracing suitable_for logic... Working through which rule matches for each call: without user_id, rule 1 (with the None default) applies since rule 2 requires an explicit value; with user_id=5, rule 1's default no longer matches so rule 2 takes over. This matches the standard Flask/Werkzeug pattern of using `defaults` dicts to pick between routes, similar to the docs example with a default page number. I'm also noting the redirect_defaults behavior Werkzeug has for these cases. Checking `provides_defaults_for`, both rules share the same endpoint and argument set, so Rule 1 can provide defaults for Rule 2 as long as it appears earlier in the rule ordering and is still suitable for the given values and method. Since registration order doesn't actually affect the outcome here, I'm now leaning toward a simpler approach: splitting the logic into two explicit view functions, `admin_user_new` and `admin_user_edit`, both calling a shared `_user_form(user)` helper, to avoid relying on this subtle Flask redirect behavior. Writing the shared form handler... I'm making sure an admin editing their own account can't demote or deactivate themselves, forcing role and active status to stay admin/1 in that case. Then I validate username, email, password, and role, and if everything checks out, I insert a new user or update the existing record accordingly. I notice a sequencing bug though: the success flash message fires before the commit actually succeeds, so if the commit fails due to a duplicate username or email, the user would see a misleading success message. I should move the flash call to happen only after the commit succeeds, and catch the IntegrityError to roll back and append an error instead. I also need to handle the edge case where a user edits their own account: since role/is_active fields are disabled in that scenario, they won't appear in the submitted form data, so when re-rendering after an error the template needs to account for missing values rather than assuming they're present. Fixing form data to reflect the forced role and is_active values on re-render, then handling the GET case for loading the user data similarly. For the template, I'm making sure passwords aren't pre-filled for security, then building the user form template with a CSRF token and form fields. I'm double-checking the username field's regex pattern, since under the `v` flag some characters like `-` need escaping inside character classes to avoid parsing errors in modern browsers. Actually I'll just skip the pattern attribute entirely and rely on server-side validation with flashed error messages, keeping the form simple. Now I'm ready to start writing out the templates, beginning with the index page. I'm building the home page template with a hero section showing the app name, a description, and conditional buttons linking to the dashboard or registration/login depending on whether the user is logged in. Then for the login page, I'm creating a form with a CSRF token, an identifier field for username or email, and starting the password field. I realize I can skip passing explicit form variables by using `request.form.get()` directly inside the templates, which simplifies both login and registration pages. Now I'm sketching the register page fields (username, email, optional full name, password, confirm) and starting the dashboard template with a greeting that falls back from full name to username. For the dashboard, I'm laying out an account summary card showing username, email, role badge, join date, and last login, plus an edit profile link, and beginning a stats card section for additional user metrics. Writing the profile form template... That default pattern falls back correctly on GET since the form is empty. Now I'm sketching the change_password template with current/new/confirm password fields, then moving to the admin_users page with a header and a button to create new users. Writing user table markup... Adding toggle and delete forms... I'm flagging a potential XSS issue: embedding the username inside a JS confirm() string within an HTML attribute could let escaped characters like `'` get decoded back into a literal quote by the browser's attribute parser, breaking out of the JS string if the username isn't restricted to safe characters. Since server validation limits usernames to a safe character set this specific app is fine, but it's still a bad pattern, so I'll switch to a generic confirmation message instead of interpolating the username. I'm also adding an empty-state row for when no users match the filter, and starting to think through the admin_user_form.html template, questioning whether a top-level `{% set %}` in a child template is actually visible inside the blocks that extend the base layout. Writing form fields... Finishing that disabled note, then checking that Jinja's inline if without else safely renders empty string when the condition is false, which confirms the checkbox state rendering from the form dict works correctly. I'm sketching the generic error page template that shows the error code, name, and description with a link back home, and now moving into writing the base CSS stylesheet with root color variables... Writing CSS variables... Adding heading sizes and navbar styles... Still writing styles... Adding focus states and button styles... Finishing the danger button style, then double-checking that hover state specificity doesn't let the outline button's background get overridden incorrectly by the base button hover rule. Now I'm defining danger button variants, giving `.btn-danger` a transparent background with danger-colored text and border, then flipping to filled danger background with white text on hover, making sure it's declared after `.btn-outline` so specificity ordering works correctly. I'm also styling a link-like button for logout actions with no background or border, inheriting font styles, plus a set of alert boxes (success, danger, warning) each with their own soft background, border, and text color palette. For the table, I'm setting up a clean layout with uppercase light-gray headers, bottom borders between rows, subtle hover highlighting, and a wrapped action cell for buttons -- though I realize making that action cell a flex container breaks its table-cell behavior. Adding hero, stats, and definition list styles... Now styling the page header, search bar, and footer with a top border separator... Adding utility classes for text alignment and button sizing, plus badge styles for different role and status indicators using colored pill shapes. For forms with multiple buttons like profile settings, I'm adding a flex container with gap spacing so they wrap nicely on smaller screens. Now I'm double-checking the Python file for errors before writing it out in full. Writing Flask app imports and config... I should verify that `g.user` as a sqlite3.Row works correctly with Jinja's bracket subscript syntax in templates. I'm also considering adding a "remember me" checkbox that sets session.permanent with a longer lifetime when checked, versus a regular browser-session cookie otherwise. I should also address session fixation through the clear() call on login, but note that since Flask sessions are client-side signed cookies, there's no true server-side invalidation — a copied old cookie stays valid until it expires, even after logout clears it client-side. This matters for password changes too, since old sessions elsewhere wouldn't be revoked automatically. Since `load_logged_in_user` re-queries the database each request with an `is_active` check, deactivated or deleted users lose access immediately, which is good — though this does mean every request, including static file requests, triggers a DB hit when logged in, which could be optimized by skipping the check for the static endpoint. Now I'm moving on to think about the register route, particularly how usernames are made case-insensitive unique. For assigning roles, I'm making the first registered user an admin by checking if the user count is zero. I'm also considering flash message categories with matching CSS styles, and confirming that Flask's `abort(400, description=...)` correctly raises a BadRequest with that description. I want to override default error messages with Spanish translations, mapping each HTTP status code (400, 403, 404) to a custom message and wiring up error handlers to use them. Still writing error handlers... SRF message or Flask's default English text for malformed requests, which is acceptable. I'm also considering whether 405 Method Not Allowed needs similar handling, since Flask's default page there would also appear in English. I'll add a Spanish 405 handler too, since typing /logout directly as GET would trigger it, improving UX. With that settled, I'm ready to assemble everything into the final deliverable: project structure, requirements.txt, app.py, templates, stylesheet, and run instructions. I'll write the response in Spanish since that's what the user used, keeping explanations concise given the length of the code. Now I'm double-checking the Jinja template details across base.html and admin_users.html — flash messages, CSRF token function, static URL building, role checks, and the for/else loop for listing users — to make sure everything lines up correctly before finalizing. I'm also verifying the login flow: the "next" redirect parameter, how the search query renders back into the template, and that secret_key/database files get mentioned for .gitignore, plus noting production deployment with waitress for Windows. For the deployment, I'm weighing WAL mode for SQLite concurrency but deciding to skip it to keep things simple, while making sure the connection setup properly configures the row factory and a reasonable timeout for locked-database scenarios. I'm confirming that commits need to happen explicitly after write operations like toggling user active status, and I'm leaving foreign key enforcement on for future-proofing even though there are no foreign keys yet. I'm also checking that comparing secrets uses a constant-time comparison to avoid timing attacks. On the CSRF front, I'm tracing how visiting the register page for the first time triggers session creation when the template calls the CSRF token helper, which means the Set-Cookie header gets sent on that very first response. I'm also double-checking that flash messages redirect properly for new visitors, and confirming the admin decorator sits correctly inside the route decorator so authorization runs before the view logic. Now I'm drafting the complete app.py structure with Spanish comments covering registration, login/logout, profile, password changes, and the admin user-management panel, starting with the necessary imports. I'm adding a function to load or generate the secret key, falling back to storing a generated token in a local file if no environment variable is set, then beginning the Flask app configuration block. I'm also planning to add a `DUMMY_HASH` constant computed at import time to use during login attempts for nonexistent users, preventing timing attacks that could reveal valid usernames. I'm restructuring the login query to check users by username or email, always running a password hash comparison (real or dummy) regardless of whether the user exists, then checking validity and active status before proceeding. Since usernames can't contain '@', there's no collision risk between a username matching another user's email. For the profile update, the stale `g.user` after an email change doesn't matter since we redirect immediately. For password changes, I'm updating the hash and committing, without needing to clear the session since it only stores minimal data. I should also note how to inspect the SQLite database directly with a query or DB Browser, and how to promote a user to admin either by being the first registered account or via a manual SQL update. On the security side, I want to flag that debug mode's interactive debugger is safe here only because the server binds to localhost by default -- exposing it publicly would be dangerous. I'm also second-guessing the password hashing method: Werkzeug 3's default `scrypt` can fail on Python builds linked against LibreSSL (like some macOS system Pythons) since `hashlib.scrypt` may not exist there, causing an AttributeError. To avoid that portability issue, it's safer to explicitly specify `pbkdf2:sha256` as the hashing method. For a tutorial app, simplicity wins—I'll just use the library default since general advice favors trusting it, and failing out of the box would be worse than marginally less hardness. For registration, revealing "already registered" is an acceptable UX tradeoff despite enumeration risk; rate limiting and email verification/password reset are good improvements to mention. I'll also include autocomplete attributes for password managers and some basic styling touches. I want to double-check the layout for labels and checkboxes, and group the profile page buttons into a form-actions wrapper. I'm also adding a .gitignore to exclude the virtual environment, cache files, database, and secret key file, then structuring the final answer with headers and concise code blocks. Now I'm writing out the complete requirements.txt and app.py, including configuration setup, secret key loading, the database schema, and the core imports needed for the Flask app with authentication and admin features. Writing app.py setup code... I'm defining helper regexes for validating usernames and emails, plus database connection management functions that open per-request SQLite connections with foreign keys enabled and properly close them on teardown. I'm noting that the init_db function should explicitly close its connection rather than relying on the context manager alone, so I'll fix that with a cleaner close pattern. Now I'm planning out the rest of the app structure—helpers, hooks, routes, and the main entry point—then moving to write the templates, starting with the base layout and footer, followed by the registration page with its CSRF-protected form. I'm adding a small password hint and then continuing with the remaining templates: login, dashboard, profile, change_password, admin_users, admin_user_form, and error pages. For change_password.html, I'm building the form with fields for current password, new password, and confirmation, each with validation attributes and a submit button. Now I'm moving to the CSS, making sure I cover all the classes referenced throughout the templates—navbar, buttons, alerts, cards, badges, stats, tables, and more—so nothing is left unstyled. Writing CSS variables and base styles... Still writing styles... Now I'm planning the overall structure of my answer: a brief intro, the stack choice of Flask with the SQLite standard library, the file tree, then the actual files, followed by run instructions. I should mention early on that I chose Python/Flask since it needs minimal dependencies, and offer to redo it in Node.js or PHP if preferred. After the code, I want to finish with features, security notes, and next steps, then do a final pass checking the Python for syntax or logic errors. Writing validation helper functions... I just caught a regex bug: the username pattern's trailing `$` would actually match before a trailing newline, so something like "abc\n" would incorrectly pass validation with `re.match`. I need to switch to `\Z` instead of `$` to anchor strictly at the true end of the string. Now I'm setting up the before-request hook that loads the logged-in user from the session, verifying they're still active in the database, and clearing the session if the user was deleted or deactivated. I also need CSRF protection: generating and storing a token in the session, exposing it to Jinja templates, and validating it on POST requests using a constant-time comparison to prevent timing attacks. Then I'm adding security headers after each response, like preventing MIME sniffing and blocking framing to guard against clickjacking. I'm checking an edge case where a deleted user's stale session gets cleared, which should properly invalidate any outdated CSRF tokens too. Now I'm drafting the shared logic for creating and editing users from the admin panel, pulling form fields like username, email, full name, password, and role while normalizing and trimming the input. For self-editing admins, I force role to admin and active status to true so they can't demote or deactivate themselves. I validate the username, email, role, and password (required on create, optional on edit), then if everything passes, insert the new user record with a hashed password into the database. Handling update logic and integrity errors... I'm finishing the form rendering logic for when validation fails or when editing an existing user versus creating a new one, falling back to default values for new accounts. I notice the variable name `error` gets reused across the password check and the error loop, so I should rename the password one to `pw_error` to avoid shadowing. Now I'm moving on to the `register` view, starting with redirecting already-logged-in users before handling the POST submission. Writing registration validation and insert logic... Building the admin users template... Adding user row details... Adding delete confirmation and empty-state row... Checking that the role badge class correctly maps to "admin" or "user" styling, and finishing up the table with a usuario count footer. I want to note that the first registered user becomes admin by default, and I should wrap up by suggesting extensions like SQLAlchemy, Flask-Login, Flask-WTF, Flask-Limiter, and Flask-Mail for password resets. I'll keep the final response in Spanish with clear headings and light use of emojis. For the final write-up, I'll label each code block with its filename as a heading, like `### app.py`. I also want to verify that the Jinja inline-if expression without an else clause is valid—it is, since omitting else just renders empty when the condition is false—and double check that the user-truthiness check in the template works correctly with the database row object. Writing user form template... Adding the active-account checkbox, a self-edit warning message, and the form action buttons... I'm checking that the minlength validation on the password field won't cause issues when left empty for existing users, and confirming is_active maps correctly from the dict, plus that editing_self defaults to false for new users. Everything checks out, so now I'll put together the final response in Spanish with clear setup instructions for different operating systems, plus a quick testing tip: register as the first user (who becomes admin), create a second regular user, then verify that logging in as that user blocks access to /admin/users with a 403. # Aplicación web con Flask + SQLite y gestión de usuarios Usé **Python + Flask**, porque SQLite ya viene incluido en Python y casi no hay dependencias. La app incluye: - Registro, login y logout (el primer usuario registrado es **administrador**) - Perfil editable y cambio de contraseña - Panel de administración para crear, editar, activar/desactivar, buscar y eliminar usuarios - Seguridad: contraseñas con hash, protección CSRF, validaciones, consultas parametrizadas y protección contra open-redirect ## Estructura ``` mi_app/ ├── app.py ├── requirements.txt ├── static/ │ └── style.css └── templates/ ├── base.html ├── index.html ├── register.html ├── login.html ├── dashboard.html ├── profile.html ├── change_password.html ├── admin_users.html ├── admin_user_form.html └── error.html ``` `database.db` y `.secret_key` se crean solos al arrancar. --- ## `requirements.txt` ``` Flask>=3.0 ``` ## `app.py` ```python """ Aplicación web con Flask + SQLite y gestión de usuarios. Incluye: registro, login/logout, perfil, cambio de contraseña y un panel de administración (crear, editar, activar/desactivar y borrar usuarios). """ import hmac import os import re import secrets import sqlite3 from datetime import timedelta from functools import wraps from flask import (Flask, abort, flash, g, redirect, render_template, request, session, url_for) from werkzeug.security import check_password_hash, generate_password_hash # ───────────────────────── Configuración ───────────────────────── BASE_DIR = os.path.abspath(os.path.dirname(__file__)) DATABASE = os.path.join(BASE_DIR, "database.db") def load_secret_key(): """Usa SECRET_KEY del entorno; si no existe, genera una y la guarda en .secret_key""" key = os.environ.get("SECRET_KEY") if key: return key path = os.path.join(BASE_DIR, ".secret_key") if os.path.exists(path): with open(path, encoding="utf-8") as f: return f.read().strip() key = secrets.token_hex(32) with open(path, "w", encoding="utf-8") as f: f.write(key) return key app = Flask(__name__) app.config.update( SECRET_KEY=load_secret_key(), SESSION_COOKIE_HTTPONLY=True, SESSION_COOKIE_SAMESITE="Lax", PERMANENT_SESSION_LIFETIME=timedelta(days=14), # SESSION_COOKIE_SECURE=True, # actívalo cuando uses HTTPS ) SCHEMA = """ CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL UNIQUE COLLATE NOCASE, email TEXT NOT NULL UNIQUE COLLATE NOCASE, full_name TEXT NOT NULL DEFAULT '', password_hash TEXT NOT NULL, role TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('user', 'admin')), is_active INTEGER NOT NULL DEFAULT 1, created_at TEXT NOT NULL DEFAULT (datetime('now')), last_login TEXT ); """ USERNAME_RE = re.compile(r"[A-Za-z0-9_.-]{3,30}") EMAIL_RE = re.compile(r"[^@\s]+@[^@\s]+\.[^@\s]+") # Hash falso: el login tarda igual exista o no el usuario DUMMY_HASH = generate_password_hash("contraseña-falsa") # ───────────────────────── Base de datos ───────────────────────── def get_db(): if "db" not in g: g.db = sqlite3.connect(DATABASE) g.db.row_factory = sqlite3.Row g.db.execute("PRAGMA foreign_keys = ON") return g.db @app.teardown_appcontext def close_db(exc=None): db = g.pop("db", None) if db is not None: db.close() def init_db(): db = sqlite3.connect(DATABASE) db.executescript(SCHEMA) db.commit() db.close() # ───────────────────────── Validaciones ───────────────────────── def validate_username(username): if not USERNAME_RE.fullmatch(username): return "El usuario debe tener 3-30 caracteres: letras, números, _ . -" return None def validate_email(email): if len(email) > 254 or not EMAIL_RE.fullmatch(email): return "El email no es válido." return None def validate_password(password): if len(password) < 8: return "La contraseña debe tener al menos 8 caracteres." if len(password) > 128: return "La contraseña no puede superar los 128 caracteres." if not re.search(r"[A-Za-z]", password) or not re.search(r"\d", password): return "La contraseña debe incluir al menos una letra y un número." return None def is_safe_url(target): """Evita open-redirects: solo se aceptan rutas internas.""" return (bool(target) and target.startswith("/") and not target.startswith("//") and "\\" not in target) # ───────────────────────── Hooks de la petición ───────────────────────── @app.before_request def load_logged_in_user(): g.user = None if request.endpoint == "static": return user_id = session.get("user_id") if user_id is not None: g.user = get_db().execute( "SELECT * FROM users WHERE id = ? AND is_active = 1", (user_id,) ).fetchone() if g.user is None: # usuario borrado o desactivado session.clear() def get_csrf_token(): if "_csrf_token" not in session: session["_csrf_token"] = secrets.token_hex(16) return session["_csrf_token"] app.jinja_env.globals["csrf_token"] = get_csrf_token @app.before_request def csrf_protect(): if request.method == "POST": expected = session.get("_csrf_token", "") received = request.form.get("_csrf_token", "") if not expected or not hmac.compare_digest(expected.encode(), received.encode()): abort(400, description="Token CSRF inválido o sesión caducada. " "Recarga la página e inténtalo de nuevo.") @app.after_request def security_headers(resp): resp.headers.setdefault("X-Content-Type-Options", "nosniff") resp.headers.setdefault("X-Frame-Options", "DENY") resp.headers.setdefault("Referrer-Policy", "same-origin") return resp # ───────────────────────── Decoradores ───────────────────────── def login_required(view): @wraps(view) def wrapped(*args, **kwargs): if g.user is None: flash("Inicia sesión para acceder a esta página.", "warning") return redirect(url_for("login", next=request.path)) return view(*args, **kwargs) return wrapped def admin_required(view): @wraps(view) def wrapped(*args, **kwargs): if g.user is None: flash("Inicia sesión para acceder a esta página.", "warning") return redirect(url_for("login", next=request.path)) if g.user["role"] != "admin": abort(403) return view(*args, **kwargs) return wrapped # ───────────────────────── Rutas públicas ───────────────────────── @app.route("/") def index(): return render_template("index.html") @app.route("/register", methods=["GET", "POST"]) def register(): if g.user: return redirect(url_for("dashboard")) if request.method == "POST": username = request.form.get("username", "").strip() email = request.form.get("email", "").strip().lower() full_name = request.form.get("full_name", "").strip()[:100] password = request.form.get("password", "") confirm = request.form.get("confirm", "") errors = [e for e in (validate_username(username), validate_email(email), validate_password(password)) if e] if password != confirm: errors.append("Las contraseñas no coinciden.") if not errors: db = get_db() # El primer usuario registrado se convierte en administrador is_first = db.execute("SELECT COUNT(*) FROM users").fetchone()[0] == 0 try: db.execute( "INSERT INTO users (username, email, full_name, password_hash, role) " "VALUES (?, ?, ?, ?, ?)", (username, email, full_name, generate_password_hash(password), "admin" if is_first else "user"), ) db.commit() except sqlite3.IntegrityError: db.rollback() errors.append("Ese usuario o email ya está registrado.") else: msg = "¡Cuenta creada! Ya puedes iniciar sesión." if is_first: msg += " Eres el administrador." flash(msg, "success") return redirect(url_for("login")) for error in errors: flash(error, "danger") return render_template("register.html") @app.route("/login", methods=["GET", "POST"]) def login(): if g.user: return redirect(url_for("dashboard")) if request.method == "POST": identifier = request.form.get("identifier", "").strip() password = request.form.get("password", "") db = get_db() user = db.execute( "SELECT * FROM users WHERE username = ? OR email = ?", (identifier, identifier), ).fetchone() stored_hash = user["password_hash"] if user else DUMMY_HASH valid = check_password_hash(stored_hash, password) and user is not None if not valid: flash("Usuario o contraseña incorrectos.", "danger") elif not user["is_active"]: flash("Tu cuenta está desactivada. Contacta con un administrador.", "danger") else: session.clear() # evita session fixation session["user_id"] = user["id"] session.permanent = bool(request.form.get("remember")) db.execute("UPDATE users SET last_login = datetime('now') WHERE id = ?", (user["id"],)) db.commit() next_url = request.args.get("next") return redirect(next_url if is_safe_url(next_url) else url_for("dashboard")) return render_template("login.html") @app.route("/logout", methods=["POST"]) def logout(): session.clear() flash("Sesión cerrada correctamente.", "success") return redirect(url_for("index")) # ───────────────────────── Rutas de usuario ───────────────────────── @app.route("/dashboard") @login_required def dashboard(): stats = None if g.user["role"] == "admin": stats = get_db().execute( """SELECT COUNT(*) AS total, COALESCE(SUM(is_active), 0) AS active, COALESCE(SUM(role = 'admin'), 0) AS admins FROM users""" ).fetchone() return render_template("dashboard.html", stats=stats) @app.route("/profile", methods=["GET", "POST"]) @login_required def profile(): if request.method == "POST": full_name = request.form.get("full_name", "").strip()[:100] email = request.form.get("email", "").strip().lower() error = validate_email(email) if error: flash(error, "danger") else: db = get_db() try: db.execute("UPDATE users SET full_name = ?, email = ? WHERE id = ?", (full_name, email, g.user["id"])) db.commit() except sqlite3.IntegrityError: db.rollback() flash("Ese email ya está en uso.", "danger") else: flash("Perfil actualizado.", "success") return redirect(url_for("profile")) return render_template("profile.html") @app.route("/profile/password", methods=["GET", "POST"]) @login_required def change_password(): if request.method == "POST": current = request.form.get("current_password", "") new = request.form.get("new_password", "") confirm = request.form.get("confirm", "") pw_error = validate_password(new) if not check_password_hash(g.user["password_hash"], current): flash("La contraseña actual es incorrecta.", "danger") elif new != confirm: flash("Las contraseñas nuevas no coinciden.", "danger") elif pw_error: flash(pw_error, "danger") else: db = get_db() db.execute("UPDATE users SET password_hash = ? WHERE id = ?", (generate_password_hash(new), g.user["id"])) db.commit() flash("Contraseña actualizada.", "success") return redirect(url_for("profile")) return render_template("change_password.html") # ───────────────────────── Panel de administración ───────────────────────── @app.route("/admin/users") @admin_required def admin_users(): q = request.args.get("q", "").strip() db = get_db() if q: like = f"%{q}%" users = db.execute( """SELECT * FROM users WHERE username LIKE ? OR email LIKE ? OR full_name LIKE ? ORDER BY id""", (like, like, like), ).fetchall() else: users = db.execute("SELECT * FROM users ORDER BY id").fetchall() return render_template("admin_users.html", users=users, q=q) def _handle_user_form(user): """Lógica común para crear (user=None) y editar usuarios.""" db = get_db() editing_self = user is not None and user["id"] == g.user["id"] if request.method == "POST": username = request.form.get("username", "").strip() email = request.form.get("email", "").strip().lower() full_name = request.form.get("full_name", "").strip()[:100] password = request.form.get("password", "") role = request.form.get("role", "user") is_active = 1 if request.form.get("is_active") else 0 if editing_self: # un admin no puede quitarse el rol ni desactivarse role, is_active = "admin", 1 errors = [e for e in (validate_username(username), validate_email(email)) if e] if role not in ("user", "admin"): errors.append("Rol no válido.") if user is None or password: # al crear es obligatoria; al editar, opcional pw_error = validate_password(password) if pw_error: errors.append(pw_error) if not errors: try: if user is None: db.execute( """INSERT INTO users (username, email, full_name, password_hash, role, is_active) VALUES (?, ?, ?, ?, ?, ?)""", (username, email, full_name, generate_password_hash(password), role, is_active), ) message = f"Usuario «{username}» creado." else: db.execute( """UPDATE users SET username = ?, email = ?, full_name = ?, role = ?, is_active = ? WHERE id = ?""", (username, email, full_name, role, is_active, user["id"]), ) if password: db.execute("UPDATE users SET password_hash = ? WHERE id = ?", (generate_password_hash(password), user["id"])) message = f"Usuario «{username}» actualizado." db.commit() except sqlite3.IntegrityError: db.rollback() errors.append("El nombre de usuario o el email ya están en uso.") else: flash(message, "success") return redirect(url_for("admin_users")) for error in errors: flash(error, "danger") form = request.form.to_dict() form["role"], form["is_active"] = role, is_active elif user is not None: form = dict(user) else: form = {"role": "user", "is_active": 1} return render_template("admin_user_form.html", user=user, form=form, editing_self=editing_self) @app.route("/admin/users/new", methods=["GET", "POST"]) @admin_required def admin_user_new(): return _handle_user_form(None) @app.route("/admin/users//edit", methods=["GET", "POST"]) @admin_required def admin_user_edit(user_id): user = get_db().execute("SELECT * FROM users WHERE id = ?", (user_id,)).fetchone() if user is None: abort(404) return _handle_user_form(user) @app.route("/admin/users//toggle", methods=["POST"]) @admin_required def admin_user_toggle(user_id): if user_id == g.user["id"]: flash("No puedes desactivar tu propia cuenta.", "warning") else: db = get_db() db.execute("UPDATE users SET is_active = 1 - is_active WHERE id = ?", (user_id,)) db.commit() flash("Estado del usuario actualizado.", "success") return redirect(url_for("admin_users")) @app.route("/admin/users//delete", methods=["POST"]) @admin_required def admin_user_delete(user_id): if user_id == g.user["id"]: flash("No puedes eliminar tu propia cuenta.", "warning") else: db = get_db() db.execute("DELETE FROM users WHERE id = ?", (user_id,)) db.commit() flash("Usuario eliminado.", "success") return redirect(url_for("admin_users")) # ───────────────────────── Errores ───────────────────────── ERRORES = { 400: ("Solicitud incorrecta", None), 403: ("Acceso denegado", "No tienes permisos para ver esta página."), 404: ("No encontrado", "La página que buscas no existe."), 405: ("Método no permitido", "Esta acción no está permitida."), } @app.errorhandler(400) @app.errorhandler(403) @app.errorhandler(404) @app.errorhandler(405) def http_error(e): titulo, mensaje = ERRORES[e.code] return render_template("error.html", code=e.code, title=titulo, message=mensaje or e.description), e.code # ───────────────────────── Arranque ───────────────────────── init_db() if __name__ == "__main__": app.run(debug=True) # ⚠️ usa debug=False en producción ``` --- ## Plantillas (`templates/`) ### `base.html` ```html {% block title %}Inicio{% endblock %} · Mi App

{% with messages = get_flashed_messages(with_categories=true) %} {% for category, message in messages %}

{% endfor %} {% endwith %} {% block content %}{% endblock %}

``` ### `index.html` ```html {% extends "base.html" %} {% block title %}Inicio{% endblock %} {% block content %}

Bienvenido a Mi App

Aplicación web con Flask y SQLite: registro, inicio de sesión, perfiles y administración de usuarios.

{% if g.user %} Ir a mi panel {% else %} Crear cuenta Iniciar sesión {% endif %}

{% endblock %} ``` ### `register.html` ```html {% extends "base.html" %} {% block title %}Crear cuenta{% endblock %} {% block content %}

Crear cuenta

¿Ya tienes cuenta? Inicia sesión

{% endblock %} ``` ### `login.html` ```html {% extends "base.html" %} {% block title %}Iniciar sesión{% endblock %} {% block content %}

Iniciar sesión

¿No tienes cuenta? Regístrate

{% endblock %} ``` ### `dashboard.html` ```html {% extends "base.html" %} {% block title %}Panel{% endblock %} {% block content %}

Hola, {{ g.user['full_name'] or g.user['username'] }} 👋

Tu cuenta

Usuario: {{ g.user['username'] }}
Email: {{ g.user['email'] }}
Rol: {{ g.user['role'] }}
Miembro desde: {{ g.user['created_at'] }} UTC
Último acceso: {{ g.user['last_login'] or '—' }}

Editar perfil

{% if stats %}

Estadísticas

{{ stats['total'] }}Usuarios

{{ stats['active'] }}Activos

{{ stats['admins'] }}Admins

Gestionar usuarios

{% endif %}

{% endblock %} ``` ### `profile.html` ```html {% extends "base.html" %} {% block title %}Mi perfil{% endblock %} {% block content %}

Mi perfil

{% endblock %} ``` ### `change_password.html` ```html {% extends "base.html" %} {% block title %}Cambiar contraseña{% endblock %} {% block content %}

Cambiar contraseña

{% endblock %} ``` ### `admin_users.html` ```html {% extends "base.html" %} {% block title %}Usuarios{% endblock %} {% block content %}

{% for u in users %} {% else %} {% endfor %}

ID	Usuario	Email	Rol	Estado	Último acceso (UTC)	Acciones
{{ u['id'] }}	{{ u['username'] }} {% if u['full_name'] %} {{ u['full_name'] }}{% endif %}	{{ u['email'] }}	{{ u['role'] }}	{% if u['is_active'] %} Activo {% else %} Inactivo {% endif %}	{{ u['last_login'] or '—' }}	Editar {% if u['id'] != g.user['id'] %} {% endif %}
No se encontraron usuarios.

{{ users|length }} usuario(s)

{% endblock %} ``` ### `admin_user_form.html` ```html {% extends "base.html" %} {% block title %}{{ 'Editar usuario' if user else 'Nuevo usuario' }}{% endblock %} {% block content %}

{% endblock %} ``` ### `error.html` ```html {% extends "base.html" %} {% block title %}Error {{ code }}{% endblock %} {% block content %}

Volver al inicio

{% endblock %} ``` --- ## `static/style.css` ```css :root { --bg: #f3f4f8; --card: #ffffff; --text: #1f2937; --muted: #6b7280; --primary: #4f46e5; --primary-dark: #4338ca; --border: #e5e7eb; --danger: #dc2626; --radius: 12px; --shadow: 0 1px 3px rgba(0,0,0,.08), 0 8px 24px rgba(0,0,0,.05); } * { box-sizing: border-box; } body { margin: 0; min-height: 100vh; display: flex; flex-direction: column; font-family: system-ui, -apple-system, "Segoe UI", Roboto, sans-serif; background: var(--bg); color: var(--text); line-height: 1.55; } a { color: var(--primary); text-decoration: none; } a:hover { text-decoration: underline; } h1 { font-size: 1.75rem; margin: 0 0 1rem; } h2 { font-size: 1.15rem; margin: 0 0 .75rem; } small, .muted { color: var(--muted); } .center { text-align: center; } .container { width: 100%; max-width: 1000px; margin: 0 auto; padding: 0 1rem; } main.container { flex: 1; padding-top: 2rem; padding-bottom: 3rem; } /* ── Navbar ── */ .navbar { background: var(--card); border-bottom: 1px solid var(--border); position: sticky; top: 0; z-index: 10; } .nav-inner { display: flex; align-items: center; justify-content: space-between; height: 60px; } .brand { font-weight: 700; font-size: 1.15rem; color: var(--text); } .brand:hover { text-decoration: none; } .navbar nav { display: flex; align-items: center; gap: 1rem; } .navbar nav a { color: var(--text); font-weight: 500; } .navbar nav a.btn { color: #fff; } .navbar nav a:hover { color: var(--primary); } .navbar nav a.btn:hover { color: #fff; } /* ── Tarjetas y layout ── */ .card { background: var(--card); border: 1px solid var(--border); border-radius: var(--radius); box-shadow: var(--shadow); padding: 1.75rem; } .card-narrow { max-width: 440px; margin: 1rem auto; } .grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(300px, 1fr)); gap: 1.25rem; } .page-header { display: flex; align-items: center; justify-content: space-between; margin-bottom: 1rem; gap: 1rem; } .page-header h1 { margin: 0; } .hero { text-align: center; padding: 4rem 1rem; } .hero h1 { font-size: 2.4rem; } .hero p { color: var(--muted); max-width: 560px; margin: 0 auto 1.75rem; font-size: 1.1rem; } .hero .btn { margin: 0 .25rem; } dl { display: grid; grid-template-columns: max-content 1fr; gap: .5rem 1.25rem; margin: 0 0 1.25rem; } dt { color: var(--muted); } dd { margin: 0; font-weight: 500; } .stats { display: flex; gap: .75rem; margin-bottom: 1.25rem; } .stats div { flex: 1; background: #f9fafb; border-radius: 10px; padding: .9rem; text-align: center; } .stats strong { display: block; font-size: 1.6rem; } .stats span { color: var(--muted); font-size: .85rem; } /* ── Formularios ── */ label { display: block; font-weight: 600; font-size: .9rem; margin-bottom: 1rem; } label small { font-weight: 400; } input[type=text], input[type=email], input[type=password], input[type=search], select { display: block; width: 100%; margin-top: .35rem; padding: .65rem .8rem; font: inherit; font-weight: 400; color: var(--text); background: #fff; border: 1px solid var(--border); border-radius: 8px; } input:focus, select:focus { outline: 2px solid rgba(79,70,229,.35); border-color: var(--primary); } input:disabled, select:disabled { background: #f3f4f6; color: var(--muted); } label.checkbox { display: flex; align-items: center; gap: .5rem; font-weight: 500; } label.checkbox input { width: auto; margin: 0; } .form-actions { display: flex; gap: .5rem; flex-wrap: wrap; } .search { display: flex; gap: .5rem; margin-bottom: 1rem; } .search input { margin-top: 0; } /* ── Botones ── */ .btn { display: inline-block; padding: .65rem 1.1rem; font: inherit; font-weight: 600; text-align: center; cursor: pointer; color: #fff; background: var(--primary); border: 1px solid var(--primary); border-radius: 8px; } .btn:hover { background: var(--primary-dark); text-decoration: none; } .btn-outline { background: transparent; color: var(--primary); } .btn-outline:hover { background: rgba(79,70,229,.08); } .btn-danger { background: transparent; color: var(--danger); border-color: var(--danger); } .btn-danger:hover { background: var(--danger); color: #fff; } .btn-small { padding: .3rem .7rem; font-size: .85rem; } .btn-block { display: block; width: 100%; margin-bottom: 1rem; } .inline { display: inline; margin: 0; } .link-button { background: none; border: none; padding: 0; font: inherit; font-weight: 500; color: var(--text); cursor: pointer; } .link-button:hover { color: var(--primary); text-decoration: underline; } /* ── Alertas ── */ .alert { padding: .8rem 1rem; margin-bottom: 1rem; border: 1px solid; border-radius: 8px; font-size: .95rem; } .alert-success { background: #ecfdf5; border-color: #a7f3d0; color: #065f46; } .alert-danger { background: #fef2f2; border-color: #fecaca; color: #991b1b; } .alert-warning { background: #fffbeb; border-color: #fde68a; color: #92400e; } /* ── Tabla ── */ .table-wrap { padding: 0; overflow-x: auto; } table { width: 100%; border-collapse: collapse; font-size: .92rem; } th, td { padding: .8rem 1rem; text-align: left; vertical-align: middle; border-bottom: 1px solid var(--border); } th { background: #f9fafb; color: var(--muted); font-size: .75rem; text-transform: uppercase; letter-spacing: .04em; white-space: nowrap; } tbody tr:last-child td { border-bottom: none; } tbody tr:hover { background: #fafafa; } .actions { display: flex; gap: .4rem; flex-wrap: wrap; } /* ── Badges ── */ .badge { display: inline-block; padding: .15rem .6rem; border-radius: 999px; font-size: .75rem; font-weight: 600; background: #e5e7eb; color: #374151; } .badge-admin { background: #ede9fe; color: #5b21b6; } .badge-user { background: #e0f2fe; color: #075985; } .badge-ok { background: #dcfce7; color: #166534; } .badge-off { background: #fee2e2; color: #991b1b; } .footer { text-align: center; color: var(--muted); font-size: .85rem; padding: 1.25rem; border-top: 1px solid var(--border); } ``` --- ## Cómo ejecutarlo ```bash cd mi_app python -m venv venv # Linux / macOS source venv/bin/activate # Windows venv\Scripts\activate pip install -r requirements.txt python app.py ``` Abre **http://127.0.0.1:5000** y prueba este flujo: 1. Ve a **Registrarse**. Ese primer usuario será **admin**. 2. En **Usuarios** crea otro usuario normal. 3. Cierra sesión y entra con ese usuario. Si intentas abrir `/admin/users`, verás un 403. Para ver los datos puedes usar `sqlite3 database.db "SELECT id, username, role FROM users;"` o la herramienta gráfica *DB Browser for SQLite*. --- ## Notas de seguridad - **Contraseñas:** se guardan con hash (`werkzeug.security`), nunca en texto plano. - **SQL:** todas las consultas usan parámetros `?`, así que no hay inyección SQL. - **XSS:** Jinja2 escapa el HTML automáticamente. - **CSRF:** todos los formularios POST llevan token. - **Sesiones:** se regeneran al iniciar sesión, y la cookie es `HttpOnly` y `SameSite=Lax`. - **Cuentas:** un admin no puede borrarse, desactivarse ni quitarse el rol a sí mismo, así que siempre queda al menos un administrador. Para producción: - Pon `debug=False`. - Define la variable de entorno `SECRET_KEY`. - Activa `SESSION_COOKIE_SECURE=True` y sirve la app por HTTPS. - Usa un servidor como `gunicorn` o `waitress`. - Añade `database.db` y `.secret_key` a tu `.gitignore`. ## Posibles mejoras - Límite de intentos de login (`Flask-Limiter`) - Recuperación de contraseña y verificación de email (`Flask-Mail`) - Paginación en el listado de usuarios - Avatares o subida de archivos - Migrar a `SQLAlchemy` o `Flask-Login` si el proyecto crece Si prefieres otra tecnología (Node.js/Express, PHP, Django…) o quieres añadir algo concreto, como recuperación de contraseña, dímelo y lo adapto.

Bienvenido a Mi App

Crear cuenta

Iniciar sesión

Hola, {{ g.user['full_name'] or g.user['username'] }} 👋

Tu cuenta

Estadísticas

Mi perfil

Cambiar contraseña

Gestión de usuarios

{{ 'Editar usuario' if user else 'Nuevo usuario' }}

{{ code }}

{{ title }}

teste