kaffeepause
Feb 01, 2010
view24246
view13
0
Goto Top

SQL Select Blob

GermansolvedQuestionDatabasesDevelopment

Wir haben eine Datenbank-Tabelle mit BLOBs. In diesen sind Dokumente (wie PDF) gespeichert. Für bestimmte Zugriffe sollen diese Dokumente verschlüsselt werden. Wir haben eine Verschlüsselung im Einsatz, nur muss ich prüfen, ob tatsächlich alle Dokumente entsprechend verfremdet wurden. Daher möchte ich gerne eine SQL-Abfrage starten, die mir anzeigt, ob eventuell noch unverfremdete Dokumente in der Tabelle vorliegen.

Hallo mal wieder,

wie gesagt:
- eine Tabelle mit BLOBs (Microsoft SQL Server 2005 auf Microsoft Windows Server 2003)
- in den Feldern PDFs, DOCs, XLS, HTML

Dateien haben ja bestimmte Dateiheader (PDF-Dateien beginnen immer mit "%pdf"), daher würde ich gerne die Tabelle nach diesen Dateiheadern durchsuchen.

Wie würde eine solche Abfrage aussehen? Kann ich nach diesem Dateiheader (wie "%pdf") suchen?
Oder kann ich nach dem entsprechenden HEX-Wert dieses Dateiheaders suchen (2550)?

Anbei Bild eines BLOBs/PDF.

1ebdf86c4167b3bbb874a894a573f24d

Viele Grüße an alle anderen IT-Crowds,

Kaffeepause
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 134865

Url: https://administrator.de/contentid/134865

Printed on: April 24, 2024 at 12:04 o'clock

13 Comments
Latest comment
Goto Top
Member: Biber
Biber Feb 01, 2010 at 16:08:14 (UTC)
Goto Top
Moin Kaffeepause,

eine undankbare Aufgabe hast du dir da aufhalsen lassen... macht (vergleichsweise) viel Aufwand mit einen vermuteten Nutzen von nahezu 0.
Denn die Erwartungshaltung ist ja sicherlich "Ja, es hat sich bestätigt, dass alle PDF-Dokumente zumindest nicht mehr mit jedem PDF-Reader einfach angezeigt werden können".

Also. der MSSQL-Server bringt keine dokumentierte native Funktion mit, um on-the-fly BLOB-Felder "lesbar" anzuzeigen, z.B wie in einem Hex-viewer bzw. in deinem Bildchen oben.

Gute Nachricht: so ein "BLOB-Reader" ist z.B. als Stored Procedure eben mal schnell zusammengeharkt.
Ein ganz nettes (ruhig angegangenes) Beispiel in 4 Akten findest du z.B im Database Journal als Storing Images and BLOB files in SQL Server Part 1-4 von Don Schlichting.

Aber ich würde überprüfen, ob nicht ein Anklicken einzelner Datensätze, also eine mehr oder weniger oberflächliche Stichproben-Ansicht ausreichen könnte.
Denn das dankt dir keiner, wenn du da eine Woche Arbeitszeit verbrätst.... und zu einem akzeptablen Preis verkaufen kannst du deine Erfahrungen auch eher nicht.

Grüße
Biber
Member: Kaffeepause
Kaffeepause Feb 01, 2010 at 16:23:11 (UTC)
Goto Top
Hallo und danke für die schnelle Antwort,

nee, Stichprobe ist leider nicht, denn das hab ich schon gemacht. Sind tatsächlich sehr sensible Daten, und es soll sichergestellt sein, dass ALLE Dokumente entfernt/verfremdet wurden. Daher dachte ich eben an eine solche Abfrage nach den Dateiheadern. Wenn ich eine solche Abfrage starten könnte (Zeige mir alle Datensätze die mit Dateiheader PDF, DOC, XLS, MSG, HTML anfangen) und dabei 0 Datensätze rauskäme, dann wäre das eine gute Überprüfung. Vor allem wenn ich diese Abfrage vorher auf der unveränderten Originaldatenbank absetzen würde und mir tatsächlich die Anzahl der Dokumente in unserer Datenbank angezeigt würde (hier würde ja ein Select Count ausreichen).

Kann ich überhaupt die Funktion SELECT in Verbindung mit BLOB verwenden?
Hat BLOB die Funktion SELECT?
Wenn ja, wie arbeitet MSSQL mit BLOBs, sprich in welchem Format?
"Liest" MSSQL die Felder tatsächlich binär? Oder Hex? Oder Reintext?

Bin leider kein Datenbankadmin...

Happy Robanukah,
Kaffeepause
Member: Biber
Biber Feb 01, 2010 at 16:55:48 (UTC)
Goto Top
Moin Kaffeepause,

ich weiß jetzt nicht, wie ich dir die Worte "der MSSQL-Server bringt keine dokumentierte native Funktion mit, um on-the-fly BLOB-Felder "lesbar" anzuzeigen" schonend und volksnah näherbringen kann...

Hast du mal den Link oben aufgerufen?

Die Hoffnung mit "Select Count() from whatever where irgendneSkalareFunktion(Blobfeld) operator 'bla%' " kannst du jedenfalls begraben AFIAK.

Grüße
Biber
Member: MadMax
MadMax Feb 01, 2010 at 22:33:38 (UTC)
Goto Top
Moin,

das mit dem select sehe ich anders als Biber. Mit einem 
select * from Tabelle where Blob like '%PDF%'
kannst Du Dir eigentlich die Daten anzeigen lassen, die die Zeichenfolge 'PDF' enthalten. Das Blob (varbinary (max)) wird dann nämlich in varchar umgewandelt und durchsucht. Generell kann man sich also die zu suchende Hexfolge in varchar umwandeln, ebenso die varbinary-Spalte und dann mit like danach suchen.

Problem ist allerdings, daß in Hexfolgen sich dann auch Joker für die Suche mit like einschleichen können, wie z.B. das erste Zeichen im PDF-Header, 0x25 = %. Mit folgender kleiner Routine läßt sich aber auch das bewerkstelligen:
declare @SuchHex varbinary (500), @SuchText varchar (500)

-- hier die zu suchende Hexfolge eintragen
select @SuchHex = 0x25504446

-- Ersetzung fuer Platzhalter
select @SuchText = convert (varchar (500), replace (replace (replace (replace (replace (
		@SuchHex, '[', '[[]'), '%', '[%]'), '^', '[^]'), '_', '[_]'), '-', '[-]'))  

select @SuchText = @SuchText + '%'	-- Suche am Anfang vom Text  
--select @SuchText = '%' + @SuchText + '%'	-- Suche ueberall im Text  
--select @SuchText = '%' + @SuchText	-- Suche am Ende vom Text  

select <Spalte>, convert (varchar (max), <Spalte>) from <Tabelle> where convert (varchar (max), <Spalte>) like @SuchText

Einfach oben die zu suchende Hexfolge eintragen und am Ende <Tabelle> und <Spalte> ersetzen. Falls nicht am Anfang, sondern überall oder am Ende gesucht werden soll, die entsprechende Zeile aktiv schalten und fertig.

Gruß, Mad Max
Member: Kaffeepause
Kaffeepause Feb 02, 2010 at 08:56:40 (UTC)
Goto Top
Hallo,

erstmal danke an beide für die Antworten!

mit
select * from Tabelle where Blob like '%PDF%'
erhalte ich
Der Argumentdatentyp varchar ist für das 2-Argument der like-Funktion ungültig.
Die Syntax der Abfrage wird allerdings von MSSQL problemlos anerkannt.

mit
select * from Tabelle where Blob like 0x2550
erhalte ich keine Fehlermeldung, allerdings auch keinen einzigen Treffer, was mich verwundert, denn nach der Joker-Erklärung habe ich eher mit zu vielen Ergebnissen gerechnet.

Mit der Routine erhalte ich eine Fehlermeldung
Meldung 529, Ebene 16, Status 2, Zeile 14
Die explizite Konvertierung des image-Datentyps in varchar(max) ist nicht zulässig.

Ich hab das Gefühl, dass das alles schon kurz vorm Ziel ist, deswegen würde ich mich sehr freuen, wenn sich die letzten Fehlermeldungen auch noch irgendwie beheben ließen.

Muss auch derweil mal schauen, was Google so zu den Fehlermeldungen sagt.

Viele Grüße,
Kaffeepause
Member: MadMax
MadMax Feb 02, 2010 at 09:26:13 (UTC)
Goto Top
Moin Kaffepause,

Ihr verwendet einen veralteten Datentyp, image. Der funktioniert zwar noch, ist aber im Prinzip abgelöst durch den Datentyp varbinary (max). Deswegen funktioniert auch die implizite Konvertierung beim einfachen select nicht. Dann muß man halt den Datentyp image erstmal in varbinary (max) konvertieren und dann erst in varchar (max). In der Suchroutine lautet die letzte Zeile dann also:
select <Spalte>, convert (varchar (max), convert (varbinary (max), <Spalte>)) from <Tabelle> where convert (varchar (max), convert (varbinary (max), <Spalte>)) like @SuchText

Gruß, Mad Max
Member: Kaffeepause
Kaffeepause Feb 02, 2010 at 11:47:46 (UTC)
Goto Top
Geil.

Einfach nur geil.

Ich bin im "normalen" Umgang mit MS schon bewandert (MCSE), aber im Bezug auf Datenbanken leider sehr wenig. Allein hätte ich das nie hingekriegt (ich kann den Code mit Mühe lesen und verstehen).

Vielen Dank!
Vielen, vielen Dank!
Member: Biber
Biber Feb 02, 2010 at 15:16:51 (UTC)
Goto Top
Moin Mad Max,

auch von mir vielen Dank... hab wieder was dazugelernt. face-wink
Die Convert( VarBinary (max), blobfeld) -Möglichkeit kannte ich noch nicht.

Grüße
Biber
Member: MadMax
MadMax Feb 02, 2010 at 19:20:27 (UTC)
Goto Top
Aber gerne doch. Und es freut mich, daß ich sogar Dir noch was Neues erzählen konnte, Biber face-wink

Gruß, Mad Max
Member: Kaffeepause
Kaffeepause Feb 04, 2010 at 13:25:37 (UTC)
Goto Top
Mist, eine Frage hab ich doch noch...
Tut mir Leid, falls diese Frage irgendwie dämlich sein sollte.

Ich möchte nach allen Datensätzen suchen, die weder Dateiheader1, noch Deateiheader2 entsprechen.
Das "weder" ist an sich leicht, da ich einfach ein "not like @suchtext" verwende.
Bei der Verknüpfung der beiden Konditionen tu ich mir grad schwer.

Folgendes habe ich erfolglos ausprobiert
  • select konvertiertes feld from tabelle where konvertiertes feld not like suchbegriff1 and where konvertiertes feld not like suchbegriff2
  • selectkonvertiertes feldfromtabellewhere (konvertiertes feldnot likesuchbegriff1) and (wherekonvertiertes feldnot likesuchbegriff2)
  • selectkonvertiertes feldfromtabellewherekonvertiertes feldnot likesuchbegriff1and not likesuchbegriff2
  • selectkonvertiertes feldfromtabellewherekonvertiertes feldnot likesuchbegriff1norsuchbegriff2//

Kurzum, ich eiere hier grad ziemlich rum...

Anbei der letzte Versuch.

declare @SuchHex1 varbinary (500), @SuchText1 varchar (500), @SuchHex2 varbinary (500), @SuchText2 varchar (500)

select @SuchHex1 = 0x00000000 -- Dateiheader1
select @SuchText1 = convert (varchar (500), replace (replace (replace (replace (replace (
		@SuchHex1, '[', '[[]'), '%', '[%]'), '^', '[^]'), '_', '[_]'), '-', '[-]'))  
select @SuchText1 = @SuchText1 + '%'  

select @SuchHex2 = 0xAAAAAA -- Dateiheader2
select @SuchText2 = convert (varchar (500), replace (replace (replace (replace (replace (
		@SuchHex2, '[', '[[]'), '%', '[%]'), '^', '[^]'), '_', '[_]'), '-', '[-]'))  
select @SuchText2 = @SuchText2 + '%'  

select convert (varchar (max), convert (varbinary (max), TABELLE)) 
from tabelle.SPALTE 
where (convert (varchar (max), convert (varbinary (max), TABELLE)) not like @SuchText1) 
and
where (convert (varchar (max), convert (varbinary (max), TABELLE)) not like @SuchText2)

Viele Grüße,
die Kaffeepause
Member: Biber
Biber Feb 04, 2010 at 15:28:14 (UTC)
Goto Top
Moin Kaffeepause,

ich weiß nicht ganz genau, aus welchem kontext du diesen Code kopiert hast, aber...

?? Ist da nicht ein bisschen zu häufig das Schlüsselwort "select" drin?

Grüße
Biber
Member: MadMax
MadMax Feb 04, 2010 at 20:56:30 (UTC)
Goto Top
Witzig, Du hast anscheinend wirklich nur die richtige Möglichkeit ausgelassen: das "where" in der letzten Zeile muß weg, also "... and (convert (varchar ..." face-wink

@Biber
Den Code hat er überwiegend von mir da oben kopiert. Und ich habe mir für normale Zuweisungen an Variablen auch select statt set angewöhnt. Funktionieren aber genauso face-wink

Gruß, Mad Max
Member: Kaffeepause
Kaffeepause Feb 05, 2010 at 11:01:04 (UTC)
Goto Top
So, jetzt aber fertig.

Vielen Dank!

Das WHERE war das Problem.
Jetzt kann ich schön mehrere Suchbegriffe verknüpfen.

Das ist ganz toll geworden.
So, wie ich es hier brauche. face-smile

Viele Grüße und nochmals vielen Dank,
die Kaffeepause
GermansolvedQuestionDatabasesDevelopment
Hotly discussed
gleixnerdCheck of ZFW Firewallgleixnerd - 5 CommentsjstrickerWireguard VPN on UDM Pro behind Fritzbox - Handshake did not completejstricker - 3 CommentsAlexWishaHow to set up and configure a Linux GRE tunnelAlexWisha - 3 Comments