Make all code SQL injection safe #490

New issue

Open

opened 2026-04-22 07:53:45 +00:00 by a24timsv · 1 comment

a24timsv commented

2026-04-22 07:53:45 +00:00

Collaborator

Some code are not written in a safe SQL manner. Some parts could suffer from SQL injections. Even if it might not make a difference, it is a good standard to always write in a safe way.

This can either be done by using bind Parms (as we did in the Database Construction course) or using procedures in the database itself.

Some code are not written in a safe SQL manner. Some parts could suffer from SQL injections. Even if it might not make a difference, it is a good standard to always write in a safe way. This can either be done by using bind Parms (as we did in the Database Construction course) or using procedures in the database itself.

a24timsv added the

help wanted

backEnd

labels

2026-04-22 07:53:45 +00:00

c24elipe commented

2026-04-22 07:59:57 +00:00

Collaborator

A possible solution (example) for a service is:

  public async Task<UserRow?> GetByEmailAsync(string email)
    {
        await using NpgsqlConnection connection = await dataSource.OpenConnectionAsync();
        const string sql = """
                           SELECT
                               id as Id,
                               username as Username,
                               email as Email,
                               password_hash as PasswordHash,
                               role as Role,
                               deleted_at as DeletedAt,
                               created_at as CreatedAt,
                               updated_at as UpdatedAt
                               
                           FROM
                               users
                           WHERE username = @Username AND deleted_at IS NULL;
                           """;
        IEnumerable<UserRow> rows = await connection.QueryAsync<UserRow>(sql, new { Username = email });
        return rows.FirstOrDefault();
    }

A possible solution (example) for a service is: ```cs public async Task<UserRow?> GetByEmailAsync(string email) { await using NpgsqlConnection connection = await dataSource.OpenConnectionAsync(); const string sql = """ SELECT id as Id, username as Username, email as Email, password_hash as PasswordHash, role as Role, deleted_at as DeletedAt, created_at as CreatedAt, updated_at as UpdatedAt FROM users WHERE username = @Username AND deleted_at IS NULL; """; IEnumerable<UserRow> rows = await connection.QueryAsync<UserRow>(sql, new { Username = email }); return rows.FirstOrDefault(); } ```