All perguntas(dba)

Ambiente

• Padrão SQL Server 2016
• PHP 8.2

Fundo

Tenho criptografia em nível de coluna configurada e totalmente funcional para várias colunas. Meu banco de dados está interagindo com sucesso com clientes por meio de PHP.

Emitir

Não consigo fazer a criptografia em nível de coluna funcionar ao tentar armazenar um arquivo. Eu estava funcionando totalmente antes da criptografia com o seguinte código (simplificado):

PHP:

$filePath = $_FILES["file_ToUpload"]["tmp_name"];       
// Read the file into a binary variable
$fileData = file_get_contents($filePath);
$fileData = unpack('H*hex', $fileData); // Convert binary data to hex
$fileData = $fileData['hex'];

// Prepare the SQL query
$query = "{CALL spUploadFile(?)}";
$params = array(
    $fileData
);
$result = sqlsrv_query($conn, $query, $params);

O procedimento SQL:

ALTER PROCEDURE [dbo].[spUploadFile]
    @FileData NVARCHAR(MAX)
AS
BEGIN  
    DECLARE @BinaryData VARBINARY(MAX) = CONVERT(VARBINARY(MAX), @FileData, 2);  -- Convert hex string back to binary
    
    INSERT INTO MyFiles (File_Data)
    VALUES (@BinaryData)
END

Minhas tentativas

Sinto que tentei de tudo. Tentei passar o $filedata como binário no PHP. Aqui está o que achei que teria mais sucesso em meu procedimento SQL:

ALTER PROCEDURE [dbo].[spUploadFile]
    @FileData NVARCHAR(MAX)
AS
BEGIN  
    OPEN SYMMETRIC KEY MY_SymmetricKey
    DECRYPTION BY CERTIFICATE MY_ColumnEncryption;
    
    Declare @EncryptedFileData VARBINARY(MAX) = ENCRYPTBYKEY(KEY_GUID('MY_SymmetricKey'), @FileData)
    
    CLOSE SYMMETRIC KEY MY_SymmetricKey;
    
    INSERT INTO MyFiles (File_Data)
    VALUES (@EncryptedFileData) 
END

No entanto, isso gera um erro: dados de string ou binários serão truncados. Eu também tentei:

Declare @EncryptedFileData VARBINARY(MAX) = ENCRYPTBYKEY(KEY_GUID('MY_SymmetricKey'), convert(varchar(MAX), @FileData))

Com os mesmos resultados. Um arquivo .txt funciona bem, mas a maioria dos outros não.

A tentativa de passar o arquivo como uma matriz de bytes também gerou um erro de truncamento:

PHP:

$filePath = $_FILES["file_ToUpload"]["tmp_name"];       
// Read the file into a binary variable
$fileData = file_get_contents($filePath);

// Prepare the SQL query
$query = "{CALL spUploadFile(array($fileData, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY), SQLSRV_SQLTYPE_VARBINARY('max'))}";
$params = array(
    $fileData
);
$result = sqlsrv_query($conn, $query, $params);

SQL:

ALTER PROCEDURE [dbo].[spUploadFile]
    @FileData VARBINARY(MAX)
AS
BEGIN  
    OPEN SYMMETRIC KEY MY_SymmetricKey
    DECRYPTION BY CERTIFICATE MY_ColumnEncryption;
    
    Declare @EncryptedFileData VARBINARY(MAX) = ENCRYPTBYKEY(KEY_GUID('MY_SymmetricKey'), @FileData)
    
    CLOSE SYMMETRIC KEY MY_SymmetricKey;
    
    INSERT INTO MyFiles (File_Data)
    VALUES (@EncryptedFileData)
END

No SQL Server, criei uma chave simétrica e codifiquei uma coluna de acordo com Uma visão geral da criptografia do SQL Server em nível de coluna

use AdventureWorks2022
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLShack@1';
CREATE CERTIFICATE Certificate_test WITH SUBJECT = 'Protect my data';
CREATE SYMMETRIC KEY SymKey_test WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Certificate_test;

-- encode using symmetric keys
ALTER TABLE AdventureWorks2022.HumanResources.Employee
        ADD BankACCNumber_encrypt varbinary(MAX)
OPEN SYMMETRIC KEY SymKey_test
        DECRYPTION BY CERTIFICATE Certificate_test;
UPDATE AdventureWorks2022.HumanResources.Employee
        SET BankACCNumber_encrypt = EncryptByKey (Key_GUID('SymKey_test'), NationalIDNumber)
        FROM AdventureWorks2022.HumanResources.Employee;
CLOSE SYMMETRIC KEY SymKey_test;
select BankACCNumber_encrypt,* from AdventureWorks2022.HumanResources.Employee

Eu decodifico com sucesso com:

-- decode
OPEN SYMMETRIC KEY SymKey_test
        DECRYPTION BY CERTIFICATE Certificate_test;
SELECT nationalIDNumber,BankACCNumber_encrypt AS 'Encrypted data',
            CONVERT(nvarchar, DecryptByKey(BankACCNumber_encrypt)) AS 'Decrypted Bank account number'
            FROM AdventureWorks2022.HumanResources.Employee

Fiz backup da chave simétrica, fingi que ela foi perdida/descartada e restaurei a chave usando:

OPEN SYMMETRIC KEY SymKey_test
        DECRYPTION BY CERTIFICATE Certificate_test
BACKUP SYMMETRIC KEY SymKey_test  
    TO FILE = 'C:\stuff\Sym_key_backup.cer'
    ENCRYPTION BY PASSWORD = 'A4FR^hhjg££fhj'
CLOSE SYMMETRIC KEY SymKey_test;  
DROP SYMMETRIC KEY SymKey_test;
RESTORE SYMMETRIC KEY key_name FROM 
    FILE = 'C:\stuff\Sym_key_backup.cer'
      DECRYPTION BY PASSWORD = 'A4FR^hhjg££fhj' 
      ENCRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4' 

Em seguida, tento decodificar, usando:

OPEN SYMMETRIC KEY key_name
        DECRYPTION BY CERTIFICATE Certificate_test;
SELECT nationalIDNumber,BankACCNumber_encrypt AS 'Encrypted data',
            CONVERT(nvarchar, DecryptByKey(BankACCNumber_encrypt)) AS 'Decrypted Bank account number'
            FROM AdventureWorks2022.HumanResources.Employee

mas não descriptografa corretamente e recebo um erro:

A chave não é criptografada usando o descriptografador especificado.

Como posso usar a chave simétrica restaurada para decodificar minha coluna codificada?

Estou tentando resolver o desafio The Blunder no banco de dados MySQL e estou encontrando um erro no que considerei ser a resposta correta:

SELECT CEIL(AVG(Salary) - AVG(CAST(REPLACE(CAST(Salary AS VARCHAR(255)), '0', '') AS DECIMAL(14,2)))) AS avg_error FROM Employees;

Em palavras simples, tentei converter o Salário (que é originalmente do tipo numérico) em um varchar, remover qualquer 0s que apareça nele e convertê-lo novamente em um número. Em seguida, calculei a média do erro e calculei o limite máximo. Porém, quando executo o código, recebo a seguinte mensagem de erro:

`Mensagem do compilador

Erro de tempo de execução

Erro (stderr)

ERRO 1064 (42000) na linha 1: Você tem um erro na sintaxe SQL; verifique o manual que corresponde à versão do seu servidor MySQL para obter a sintaxe correta para usar perto de 'VARCHAR(255)), '0', '') AS DECIMAL(14,2)))) AS avg_error FROM Employees' na linha 1

Sua saída (stdout)

~ nenhuma resposta no stdout ~`

Não sei onde cometi um erro. Você poderia me dizer como posso consertar isso e se existe outra maneira de resolver?

Usando o Azure SQL, tenho um procedimento assinado por certificado no qual gostaria de ler as estatísticas da tabela. O proc pode ler sys.statsbem, mas recebo zero linhas de uma consulta usando sys.dm_db_stats_properties, por exemplo FROM sys.stats S CROSS APPLY sys.dm_db_stats_properties(S.object_id,S.stats_id) SP WHERE S.object_id= 123456. Apliquei as seguintes permissões ao usuário ao qual o certificado está vinculado...

GRANT VIEW PERFORMANCE DEFINITION TO Maintenance_User

GRANT VIEW DATABASE PERFORMANCE STATE TO Maintenance_User

GRANT VIEW DEFINITION TO Maintenance_User

GRANT VIEW DATABASE STATE TO Maintenance_User

Estou faltando outro GRANT? Preciso aplicar uma permissão diferente?

Eu tenho um banco de dados PostgreSQL com várias colunas de string em uma tabela com um índice trigrama usando pg_trgm. Eles são questionados assim

SELECT [...] FROM mytable WHERE mycolumn ILIKE '%searchterm%';

Para consultas com termos de pesquisa muito específicos, isso funciona muito bem. Existem algumas centenas de milhares de linhas nessa tabela, e algumas dessas colunas podem conter uma grande quantidade de texto (embora a maioria delas sejam strings razoavelmente curtas).

O problema é que isso não funciona bem quando os termos de pesquisa são comuns e aparecem em muitas linhas. Para consultas de pesquisa com termos de pesquisa específicos, essa consulta é concluída em milissegundos; para termos que correspondem a aproximadamente 80% de todas as linhas, leva dezenas de segundos. Os índices trigramas são usados ​​em quase todas as consultas, mesmo as lentas.

Eu sei que isso provavelmente é inerente ao modo como esse índice funciona e não pode ser facilmente corrigido, mas estou procurando principalmente maneiras de lidar melhor com esses casos do que apenas esperar o tempo limite da consulta. De qualquer forma, pesquisar esses termos não é muito útil, pois não filtrará linhas suficientes. Mas não posso impedir que os usuários insiram esses termos, então gostaria de falhar com um pouco mais de elegância do que com um tempo limite de consulta, se possível.

Primeiro, há alguma maneira de aumentar o desempenho desse tipo de consulta? Existem tipos melhores de índices para esta ou outras maneiras de ajustar o desempenho do índice? Estou usando intencionalmente índices de trigramas aqui e não a pesquisa de texto completo do Postgres, pois a lematização seria contraproducente aqui. Os termos de pesquisa geralmente não são palavras em inglês e, em meus primeiros experimentos, a derivação foi prejudicial para certas consultas, pois, por exemplo, cortou partes de abreviações ou termos específicos do domínio.

Existe uma maneira de detectar casos como este em que o termo de pesquisa é excessivamente inespecífico? Se não for possível fazer essas consultas com rapidez suficiente, a próxima melhor coisa seria não executá-las e atingir o tempo limite, mas mostrar ao usuário um feedback útil muito mais cedo de que seus termos de pesquisa são problemáticos.

De vez em quando, a coluna run_duration do msdb.dbo.sysjobhistory retorna um valor negativo para um dos trabalhos, veja o exemplo abaixo.

O trabalho foi concluído com sucesso no momento da consulta. Alguma ideia do que poderia ser uma razão para isso?

Estamos discutindo o SQL 2019 (15.0.4322.2). Até agora, isso aconteceu apenas 2 ou três vezes nos últimos dias, com apenas um único trabalho executado na frequência de 5m. O trabalho foi executado sem esse problema há meses. Nenhum outro trabalho apresenta esse comportamento, mas é difícil dizer se é uma regra...

Este é um trabalho criado pelo usuário. Causa apenas um pequeno problema com o monitoramento dos trabalhos de longa duração (já tratei da exceção que isso causa). Só estou me perguntando o que poderia causar esse comportamento.

Nosso administrador de banco de dados saiu e como sou a única pessoa que conhece SQL, estou assumindo essas funções, mas ainda estou aprendendo.

Temos um servidor com vários bancos de dados SQL Server.
Eu os configurei para recuperação total para minimizar a perda de dados.
No fim de semana, descobri que um dos bancos de dados tinha um arquivo de log que ficava fora de controle e eventualmente preenchia o disco, e os backups dos arquivos de log de transações também haviam preenchido a unidade de backup, o que felizmente descobri antes que pudesse causar muitos problemas.

Fiz um arquivo reduzido no log do banco de dados e removi muitos backups de log de transações mais antigos, então tudo está bem no momento.

Estou tentando descobrir a melhor estratégia para reter backups e, como esta é uma área na qual sou novo, adoraria fazer uma verificação de integridade.

Atualmente, temos um backup completo semanal todos os domingos à noite, um backup diferencial diário nas outras noites e, para garantir que nada seja perdido durante o dia, backups de log de transações a cada 15 minutos.
Os backups completos semanais também são movidos para fora do local, para que não tenhamos um único ponto de falha.

Meu pensamento é, para economizar espaço, que uma vez que um backup completo aconteça no domingo, os backups diferenciais e de log de transações não sejam mais necessários, e então talvez eles possam ser simplesmente eliminados, e então eu manteria vários meses de semanalmente backups por motivos de segurança.
Ou seria uma má ideia?

Tenho duas tabelas, uma contendo enquetes e outra contendo votos de enquetes, assim (simplificada):

CREATE TABLE IF NOT EXISTS polls (
  id SERIAL PRIMARY KEY,
  token VARCHAR(20)
);
CREATE TABLE IF NOT EXISTS poll_votes (
  id SERIAL PRIMARY KEY,
  account VARCHAR(255) NOT NULL,
  poll_id INT NOT NULL,
  token_one BIGINT,
  token_two BIGINT,
  token_three BIGINT
  [etc.]
);

Há um número crescente de colunas de tokens na poll_votestabela, podendo haver mais de mil delas. Existem várias poll_voteslinhas para cada polllinha e preciso calcular quantos tokens no total estão votando em cada enquete. A polls.tokencoluna contém o nome da coluna do token a ser utilizada, ou seja, especifica qual token deve ser utilizado para contar os resultados daquela enquete específica.

Portanto, tenho uma consulta como esta para calcular os resultados quando há, digamos, apenas uma coluna de token:

SELECT p.token,
  (
    SELECT SUM(token_one)
    FROM poll_votes pv
    WHERE p.id = pv.poll_id
    GROUP BY pv.poll_id
  ) AS total_token_one
FROM polls p;

No entanto, isso codifica a token_onecoluna. Considerando que eu preciso que ele seja definido dinamicamente com base no valor que existe na tokencoluna da pollstabela. Como eu posso fazer isso?

Atualizar:

Eu criei essa estrutura de tabela (mesmo que ela crie desafios) porque há um número grande e continuamente crescente de tokens. Se houver uma linha separada poll_votespara cada token, cada voto poderá exigir a inserção de centenas de linhas (já que cada conta pode ter centenas de saldos de token). Portanto, para um grande número de contas de votação, torna-se um grande número de linhas.

Ficarei grato se conseguir ajuda com esta sintaxe SQL. Eu tenho a tabela abaixo. Quero somar as colunas localAmount_Dr e localAmount_Cr, subtrair localAmount_Cr da soma total localAmount_Dr para obter uma nova coluna chamada Balance. Quero também agrupá-los por Y/M.

Minha sintaxe SQL é a seguinte

select
date_format(CBDate, '%Y-%M')
sum(localAmount_Dr) as total_donation, 
sum(localAmount_Cr) as total_disbursement, 
(sum(localAmount_Dr) - sum(localAmount_Cr)) as balance  
from ccfdb.ccf_gencashbook  
where month(CBDate) = 04
group by CBDate, localAmount_Dr, localAmount_Cr;

A sintaxe acima me dá os seguintes resultados que não estou esperando.

O que eu quero alcançar é que eu quero todas as quatro colunas com a primeira coluna sendo uma data de linha única seguida pelo resto das colunas como mostrado no diagrama abaixo, que não é o resultado exato que eu quero (eu quis dizer que a tabela abaixo é não é o resultado exato que desejo, embora seja semelhante).

Meu desafio é como modificar a sintaxe SQL acima para adicionar uma data agrupada de uma linha (ou seja, 2024-abril) como a primeira coluna.

Ficarei grato se minha sintaxe puder ser modificada para me fornecer os resultados desejados. Obrigado

Atualmente, estou tentando classificar os resultados de duas tabelas de forma que o campo de classificação primário seja um na tabela filho, mas gostaria de evitar que os resultados da tabela pai fossem movidos fora da sequência.

Um exemplo seriam as seguintes tabelas (usando o Ms SQL Server):

CREATE TABLE tier_a(
   idno INTEGER NOT NULL,
   name VARCHAR(10) NOT NULL
);

CREATE TABLE tier_b(
   idno INTEGER NOT NULL,
   tier_a_idno INTEGER NOT NULL,
   name VARCHAR(10) NOT NULL
);

INSERT INTO tier_a VALUES(3, 'C');
INSERT INTO tier_a VALUES(2, 'B');
INSERT INTO tier_a VALUES(1, 'A');

INSERT INTO tier_b VALUES(1, 1, 'A');
INSERT INTO tier_b VALUES(4, 2, 'C');
INSERT INTO tier_b VALUES(2, 1, 'B');
INSERT INTO tier_b VALUES(5, 3, 'A');
INSERT INTO tier_b VALUES(3, 1, 'C');

Se eu classificar apenas pela tabela filho:

SELECT tier_a.name AS tier_a_name, tier_b.name AS tier_b_name
FROM tier_a
JOIN tier_b ON tier_b.tier_a_idno = tier_a.idno
ORDER BY tier_b_name ASC

Eu consigo o que espero:

Ascending by child

tier_a_name | tier_b_name
C           | A
A           | A
A           | B
A           | C
B           | C

Descending by child

tier_a_name | tier_b_name
B           | C
A           | C
A           | B
C           | A
A           | A

Mas o que estou tentando alcançar é manter a ordem da tabela pai:

Ascending by child

tier_a_name | tier_b_name
A           | A
A           | B
A           | C
C           | A
B           | C

Descending by child

tier_a_name | tier_b_name
A           | C
A           | B
A           | A
B           | C
C           | A

Posso simular isso com um manual terrível ORDER BY:

Ascending by child

SELECT tier_a.name AS tier_a_name, tier_b.name AS tier_b_name
FROM tier_a
JOIN tier_b ON tier_b.tier_a_idno = tier_a.idno
ORDER BY
   CASE
      -- Parent of the Lowest tier_b name (A)
      WHEN tier_a.idno = 1 THEN 1
      -- Parent of the Lowest tier_b name (A) but Higher tier_a idno
      WHEN tier_a.idno = 3 THEN 2
      -- Parent of the Highest tier_b name (C)
      WHEN tier_a.idno = 2 THEN 3
   END,
   tier_b_name ASC

Descending by child

SELECT tier_a.name AS tier_a_name, tier_b.name AS tier_b_name
FROM tier_a
JOIN tier_b ON tier_b.tier_a_idno = tier_a.idno
ORDER BY
   CASE
      -- Parent of the Highest tier_b name (C)
      WHEN tier_a.idno = 1 THEN 1
      -- Parent of the Highest tier_b name (C) but Higher tier_a idno
      WHEN tier_a.idno = 2 THEN 2
      -- Parent of the Lowestest tier_b name (A)
      WHEN tier_a.idno = 3 THEN 3
   END,
   tier_b_name DESC