[SQL] Character escaping

[YellowOnline]

Ik ben geen DBA, maar moet geregeld wel een SQL query schrijven om data op te halen uit een of andere DB. Het principe van character escaping tegen SQL injectie ken ik wel, maar in praktijk heb ik daar nog nooit mee te doen gehad. Bij deze wel, al heeft het niets met security te maken Ik gebruik in een van mijn scripts een query om op te vragen welke software geïnstalleerd staat op bepaalde computers. Die query zit er als volgt uit:

SQL:

SELECT sys.Netbios_Name0, arp.InstallDate0, arp.DisplayName0 FROM v_R_System sys JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID JOIN v_FullCollectionMembership fcm ON sys.ResourceID=fcm.ResourceID WHERE arp.DisplayName0 LIKE '$SearchString' AND fcm.CollectionID='SMS00004'

Alles ging goed tot ik vandaag een gekke error kreeg:

code:

Exception calling "Fill" with "1" argument(s): "Incorrect syntax near '09'.
Unclosed quotation mark after the character string ''."
At \\PATH\Script.ps1
:1201 char:18
+     $SqlAdapter.Fill <<<< ($SqlDataSet) | Out-Null
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Wat verboser werken leerde me dat een nieuwe applicatie die ergens opdook deze error veroorzaakte, en natuurlijk zag ik meteen waarom:

SQL:

SELECT sys.Netbios_Name0, arp.InstallDate0, arp.DisplayName0 FROM v_R_System sys JOIN v_Add_Remove_Programs arp ON sys.R
esourceID = arp.ResourceID JOIN v_FullCollectionMembership fcm ON sys.ResourceID=fcm.ResourceID WHERE arp.DisplayName0 L
IKE 'Articulate Studio '09 Pro' AND (sys.Netbios_Name0 = '') AND fcm.CollectionID='SMS00004'

Articulate Studio '09 Pro: We hebben honderden softwarepakketten maar dit is het eerste dat ik tegenkom dat een apostrof in zijn naam bevat en aldus de query natuurlijk breekt.

Hamvraag: hoe los ik het op? Wat googlen leert me dat dit een courant probleem is waarbij men de apostrof escaped met een andere apostrof. Alle voorbeelden die ik zag waren echter met statische input en niet met een variabele.

Daar valt een mouw aan te passen door, bijvoorbeeld eerst ergens:

C#:

If ($SearchString.Contains"'"){$SearchString.Replace("'","''"}

Erg elegant lijkt mij dat evenwel niet en behalve de ' zullen er vast nog karakters zijn die ik moet escapen (_, % en god weet wat nog). Ik hoor graag wat meningen van mensen die meer in SQL thuis zijn.

En alvorens iemand anders het post:

[Big Womly]

Oracle DB? Dan kan je je like statement uitbreiden met een escape statement:

SQL:

select *
from table
where column like 'text!_%' escape '!'

Dit zal alle waarden weergeven die beginnen met "text_"

Maar dit lijkt me beter op te lossen via een prepared statement. Dan wordt je SQL querry gecompiled en later de parameters ingevuld. Die parameters mogen dan single quotes, underscores, etc. bevatten.

[ Voor 37% gewijzigd door Big Womly op 24-01-2013 16:29 ]

[storeman]

Wat jij wilt noemt men escapen. Ik ben niet thuis in C#, maar even googlen op

"C# mysql escape string"

geeft:
MySql.Data.MySqlClient.MySqlHelper.EscapeString(q)

Daarnaast is de contains controle natuurlijk niet nodig. Je kunt er gewoon een replace overheen gooien.

Edit:
Ik had aangenomen dat je MySQL gebruikte, maar die aanname klopt natuurlijk niet. Waarschijnlijk heeft je database adapter hier wel een functie voor, maar dan moet je wel weten welke database er draait.

[ Voor 27% gewijzigd door storeman op 24-01-2013 16:27 ]

[Orion84]

^^wat hij zegt. Waarom zouden standaard escape methodes voor jou niet werken? Beter alternatief, wat ook in C# vast mogelijk is, is het gebruik van prepared statements.

Je kan wel aangeven dat het hier niet om security gaat, maar dezelfde oplossingen zijn van toepassing en bovendien heb je natuurlijk wel degelijk een security issue te pakken op je huidige manier. Net zoals in de xcdk comic, kan er ook een interne boosdoener zijn die een slimme applicatienaam verzint en installeert/registreert in het systeem dat jij queriet en boem, DB is stuk/lek.

[ Voor 26% gewijzigd door Orion84 op 24-01-2013 16:30 ]

[YellowOnline]

De DB is een MSSQL en de taal is Powershell overigens, maar ik gebruiker C# als lexer omdat dit nog het beste in de buurt komt.

Orion84 schreef op donderdag 24 januari 2013 @ 16:29:
^^wat hij zegt. Waarom zouden standaard escape methodes voor jou niet werken? Beter alternatief, wat ook in C# vast mogelijk is, is het gebruik van prepared statements.
(...)

Big Womly schreef op donderdag 24 januari 2013 @ 16:26:

Maar dit lijkt me beter op te lossen via een prepared statement. Dan wordt je SQL querry gecompiled en later de parameters ingevuld. Die parameters mogen dan single quotes, underscores, etc. bevatten.

Dat is wat ik doe met dit idee, nee?

C#:

If ($SearchString.Contains"'"){$SearchString.Replace("'","''"}

storeman schreef op donderdag 24 januari 2013 @ 16:26:
...

Daarnaast is de contains controle natuurlijk niet nodig. Je kunt er gewoon een replace overheen gooien.
...

Ik had verwacht dat dat een error zou geven zonder eerst de contains te doen, maar na even testen blijkt dat inderdaad niet het geval en kan die contains inderdaad weg.

[ Voor 90% gewijzigd door YellowOnline op 24-01-2013 16:42 ]

[Orion84]

Wellicht dat je eens kan kijken naar het uitvoeren van stored procedures vanuit powershell (op google genoeg over te vinden).

Zodat je niet zelf in powershell strings aan elkaar gaat plakken tot een query, maar de query vooraf definieert en daarna uitvoert met (variabele) parameters.

[ Voor 39% gewijzigd door Orion84 op 24-01-2013 16:34 ]

[YellowOnline]

Orion84 schreef op donderdag 24 januari 2013 @ 16:34:
Wellicht dat je eens kan kijken naar het uitvoeren van stored procedures vanuit powershell (op google genoeg over te vinden).

Zodat je niet zelf in powershell strings aan elkaar gaat plakken tot een query, maar de query vooraf definieert en daarna uitvoert met (variabele) parameters.

Wel, in mijn testmodus...

C#:

If ($Test)
    {
    $FirstPassed = $False
    ForEach ($Computer in $Computers)
        {
        If ($FirstPassed -EQ $False)
            {
            $Selection = "(sys.Netbios_Name0 = '" + $($Computer.Name) + "'"
            $FirstPassed = $True
            }
        Else
            {
            $Selection = $Selection + " OR sys.Netbios_Name0 = '" + $($Computer.Name) + "'"
            }
        }
    $Selection = $Selection + ")"
    $SqlQuery = "SELECT sys.Netbios_Name0, arp.InstallDate0, arp.DisplayName0 FROM v_R_System sys JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID JOIN v_FullCollectionMembership fcm ON sys.ResourceID=fcm.ResourceID WHERE arp.DisplayName0 LIKE '$SearchString' AND $Selection AND fcm.CollectionID='SMS00004'"
    }

Dat is dus niet zo wanneer het programma zelf draait, maar enkel als ik op een kleinere set computers werk om een hoop nutteloze data te vermijden (en mijn testtijd van enkele uren naar een kwartiertje terugbrengt).

---

Maar begrijp ik het goed of is wat jullie verstaan onder prepared statements wat ik zelf als oplossing voor ogen had? Dat zou ook steek houden met de comic, nl. sanitizing input ipv. via de SQL code zelf in te grijpen; tenzij ik terminologisch de bal verkeerd sla natuurlijk.

Dan doe ik gewoon alvorens de SQL string in mijn SQL object te proppen iets genre

C#:

$SearchString = $SearchString.Replace("'","''").Replace("%","'%").Replace("_","'_")

[ Voor 33% gewijzigd door YellowOnline op 24-01-2013 16:49 ]

[Orion84]

Wat is je punt, t.o.v. mijn tip over stored procedures?

Ik heb geen ervaring met stored procedures in powershell/MSSQL, maar wel met het algemene principe. Wat ik er van begrijp is dat je daarmee het hele statische deel van je query definieert als stored procedure, met daarin de variabelen (vergelijkbaar met het laatste stukje powershell code dus). Vanuit powershell roep je vervolgens die stored procedure aan en geef je de betreffende parameters mee.

Het stored procedure mechanisme zou er dan (denk ik) voor moeten zorgen dat je geen problemen krijgt met tekens in je parameters die de query zelf beïnvloeden.

Stored procedures is dus niet wat je zelf knutselt. Het heeft niks te maken met de voorbeelden die jij gaf waarin je zelf handmatig (of met standaard functies) een string gaat escapen. Het is echt een specifiek mechanisme van je DB, of framework. Zoek het gewoon eens op en je begrijpt hopelijk wat ik bedoel

[ Voor 24% gewijzigd door Orion84 op 24-01-2013 16:48 ]

[YellowOnline]

Orion84 schreef op donderdag 24 januari 2013 @ 16:44:
Wat is je punt, t.o.v. mijn tip over stored procedures?

Ik heb geen ervaring met stored procedures in powershell/MSSQL, maar wel met het algemene principe. Wat ik er van begrijp is dat je daarmee het hele statische deel van je query definieert als stored procedure, met daarin de variabelen (vergelijkbaar met het laatste stukje powershell code dus). Vanuit powershell roep je vervolgens die stored procedure aan en geef je de betreffende parameters mee.

Het stored procedure mechanisme zou er dan (denk ik) voor moeten zorgen dat je geen problemen krijgt met tekens in je parameters die de query zelf beïnvloeden.

Stored procedures is dus niet wat je zelf knutselt. Het heeft niks te maken met de voorbeelden die jij gaf waarin je zelf handmatig (of met standaard functies) een string gaat escapen. Het is echt een specifiek mechanisme van je DB, of framework. Zoek het gewoon eens op en je begrijpt hopelijk wat ik bedoel

Oh, die reactie sloeg louter op het concateneren van strings tot een query, nl. dat ik dat dus wel doe in een testmodus. Stored procedures moet ik inderdaad opzoeken, maar dat zal voor morgen zijn want ik doe iets teveel tegelijk nu (andere scripts + SCCM PSS die me een BSOD geeft + een DPM server die aan zijn maximum RPs zit + deze thread aanmaken en lezen) en ik moet om 17:00 weg.

Ik vraag me alleen nog steeds af of mijn begrip van prepared statements klopt of dat dit een heel ander concept is dat bij die stored procedures hoort. Maar goed: morgen google ik met de input van hier.

Alvast bedankt voor jullie opmerkingen!

[Orion84]

Prepared statements en stored procedures zijn volgens mij hetzelfde concept, of in elk geval sterk vergelijkbaar. Het is een mechanisme om in je script/programma of in je database 'query templates' te definiëren met daarin posities voor variabelen.

In plaats van dat je dan zelf de complete string opbouwt doe je iets als "runprocedure <procedurename> <parameter1> <parameter2>". Het 'runprocedure' mechanisme zorgt er vervolgens voor dat die parameters op de juiste manier worden ingevuld voordat de query wordt uitgevoerd, zodat je daar geen errors/security bugs door hebt.

Dus nee, je begrip van prepared statements klopt niet

[markvt]

Zo kan je je query ook opbouwen

declare @voorbeeld AS nvarchar(max)
set @voorbeeld= '[e-mail]'
select * from SysteemGebruikers where email_Address = @voorbeeld

In een stored procedure neem je dan @voorbeeld als een parameter die je meegeeft

[Gomez12]

prepared statements en stored procedures zijn 2 verschillende dingen...

Simpel gezegd is een stored procedure een stukje code in je db gezet. In dat stukje code kunnen dan wel weer prepared staments zitten door de manier van aanroepen

Prepared statements zijn gewoon 2-traps raketten. In stap 1 zeg je tegen de database : Je gaat zometeen een query krijgen die gaat dit en dat bevatten in dit en dat variabele type.
Stap 2 is daadwerkelijk de variabelen doorgeven zodat de query gerund kan worden.
Omdat je hierbij losse variabelen doorgeeft met gedefinieerde types hoeft er geen escaping oid meer plaats te vinden.

Concattenated statements is simpelweg : Hier db heb je een string, parse jij er zelf even de variabelen en de types uit, doe jij het verkeerd dan heb ik altijd nog een backup van gisteren.

En tja, escaping op ' kan nog wel eens leuke effecten hebben als je het niet compleet aanpakt. Ik weet nog wel een webshop die het ongeveer zo deed als jij, toen kwam er een scriptkiddie langs en die gooide ' in utf-8 notatie erin en dan kwam hij door de string-replace maar de db zag geen verschil

[RobIII]

Gomez12 schreef op donderdag 24 januari 2013 @ 22:32:
En tja, escaping op ' kan nog wel eens leuke effecten hebben als je het niet compleet aanpakt. Ik weet nog wel een webshop die het ongeveer zo deed als jij, toen kwam er een scriptkiddie langs en die gooide ' in utf-8 notatie erin en dan kwam hij door de string-replace maar de db zag geen verschil

offtopic:
Enig idee welk RDBMS dat is geweest? AFAIK is geen enkele daarvoor gevoelig (geweest)? Juist omdat ze specifiek zoeken naar Asc(39) zeg maar, en niet elk willekeurig teken dat eruit ziet als een single quote.

[markvt]

SQL Server:

DECLARE @S NVARCHAR(4000);
SET @S = 'SELECT * FROM X WHERE Y = ' + CAST(0x27 AS NVARCHAR(4000)) + 'wi' + CAST(0x27 AS NVARCHAR(4000));
SELECT @S


Ik ga niet volledig uitwerken maar je kan met CAST een aardige query opbouwen...
http://serverfault.com/qu...ction-db-was-sql-injected

[Gomez12]

RobIII schreef op donderdag 24 januari 2013 @ 22:38:
[...]

offtopic:
Enig idee welk RDBMS dat is geweest? AFAIK is geen enkele daarvoor gevoelig (geweest)? Juist omdat ze specifiek zoeken naar Asc(39) zeg maar, en niet elk willekeurig teken dat eruit ziet als een single quote.

Replacen werd niet in het rdbms gedaan, maar met stringfuncties in asp of php.

Volgorde was (even uit het hoofd)
replace '
concat een query
db-framework doet wat magie om utf-8 om te zetten naar isoxxx (pagina was isoxxx, rdbms was isoxxx, connectie naar rdms was isoxxx dus niemand had de magie opgemerkt want hij trad niet op als er geen utf-8 gestuurd werd, totdat iemand niet meer vanaf de pagina ging posten)
verstuur query naar server

De TS doet de replace volgens mij nu ook niet in het rdbms maar juist in powershell. Als er dan ergens in powershell of in de connectie naar de db wat magie gebeurt (vanwege verkeerde instellingen) dan kan er dus iets anders richting de server gaan dan er gechecked is...

[RobIII]

markvt schreef op donderdag 24 januari 2013 @ 23:54:
SQL Server:

DECLARE @S NVARCHAR(4000);
SET @S = 'SELECT * FROM X WHERE Y = ' + CAST(0x27 AS NVARCHAR(4000)) + 'wi' + CAST(0x27 AS NVARCHAR(4000));
SELECT @S

Hoe is dat in hemelsnaam relevant hier? Sterker nog: het is een héél erg omslachtige manier om

SQL:

select 'SELECT * FROM X WHERE Y = ''wi'''

te schrijven... _{(En geloof me, ik ben écht wel bekend met hoe SQL Injection / escapen werkt hoor )}
De link die je aanhaalt is relevant nadat je doormiddel van SQL injectie zaken op de DB gaat proberen uitvoeren. Het hele verhaal hier is nou net die SQL injectie mogelijkheid voorkomen.

markvt schreef op donderdag 24 januari 2013 @ 23:54:
Ik ga niet volledig uitwerken maar je kan met CAST een aardige query opbouwen...
http://serverfault.com/qu...ction-db-was-sql-injected

Je hoeft voor mij niets uit te werken hoor Ook zonder een CAST kun je een aardige query opbouwen. Dit is mogelijk nog irrelevanter dan de rest vanje post
_{...en dan ga ik niet eens beginnen over waarom je al die extra tekens zou typen c.q. al die bytes zou verspillen door nvarchar(4000) te gebruiken i.p.v. gewoon char(1) als je dan toch per-sé wil casten.}

Gomez12 schreef op vrijdag 25 januari 2013 @ 00:11:
[...]

Replacen werd niet in het rdbms gedaan, maar met stringfuncties in asp of php.

Aaah, zooo. Mja, nog vaag, maar kan me er iig al beter iets bij voorstellen.

Een replace (voor 't specifieke single-quote ( ' dus) geval) is overigens ook geen rocket science:

C#:

x = "Articulate Studio '09 Pro".Replace("'","''");

Waarmee ik niet zeg dat je 't zo maar moet gaan doen. Zelfs queries gaan liggen flansen ("concatenaten") is zooo 90's Gewoon lekker parameterized queries (en desnoods stored procedures, wat overigens niet hetzelfde is) gebruiken.

[ Voor 66% gewijzigd door RobIII op 25-01-2013 00:23 ]

[Gomez12]

RobIII schreef op vrijdag 25 januari 2013 @ 00:12:
[...]
Een replace (voor 't specifieke single-quote ( ' dus) geval) is overigens ook geen rocket science:

C#:

x = "Articulate Studio '09 Pro".Replace("'","''");

Moet je dat dus niet als 1e functie gaan doen, dan verder werken met x en ergens halverwege nog een htmldecode over x heengooien.

Het is op zich geen rocket science, maar je moet het wel goed qua volgorde doen en echt enkel voor sql gaan escapen.
Wat te vaak gebeurt is dat mensen 1 sanitizer functie gaan maken waar ze alle input doorheen gooien en daarna zou het maar voor elk gebruik "veilig" moeten zijn. Als je echt gaat sanitizen voor elke output dan is het geen rocket science en kan er weinig misgaan, maar mijn ervaring is dat de meeste mensen dat niet doen zegmaar.

[RobIII]

Gomez12 schreef op vrijdag 25 januari 2013 @ 00:24:
[...]

Moet je dat dus niet als 1e functie gaan doen, dan verder werken met x en ergens halverwege nog een htmldecode over x heengooien.

Het is op zich geen rocket science, maar je moet het wel goed qua volgorde doen en echt enkel voor sql gaan escapen.
Wat te vaak gebeurt is dat mensen 1 sanitizer functie gaan maken waar ze alle input doorheen gooien en daarna zou het maar voor elk gebruik "veilig" moeten zijn. Als je echt gaat sanitizen voor elke output dan is het geen rocket science en kan er weinig misgaan, maar mijn ervaring is dat de meeste mensen dat niet doen zegmaar.

...seen it al before bro
_{(Overigens zal een html-decode van '' gewoon '' geven en dus weinig stuk maken (noch html-encode for that matter), maar dat is niet 't punt hier)}
Maar ik denk dat we weer een beetje richting on-topic moeten

[ Voor 9% gewijzigd door RobIII op 25-01-2013 00:27 ]

[markvt]

Was niet helemaal duidelijk met mijn reactie, maar puur ' escapen is niet voldoende je moet ook echt valideren of de input klopt.

Als je bijvoorbeeld een getal hebt in je query:
select x from y where id = 1

Als je alleen ' escaped en ze mikken dit soort queries achter die 1

;DECLARE @S NVARCHAR(4000);SET @S=CAST(0x06F007200 AS NVARCHAR(4000));EXEC @S;

Dan kunnen ze een andere query uitvoeren ondanks dat de ' vervangen is

--

Maar voor de topic starter is het wellicht genoeg om de ' te vervangen door ''.

[RobIII]

Als je goed escaped (punt van de discussie hier) krijg je die code echt niet uitgevoerd hoor Daar hoef je niets voor te valideren... (Los daarvan: again, gewoon prepared statements gebruiken dan is deze hele discussie overbodig).

Wat jij telkens post oogt misschien wel spannend maar is vooral erg nutteloos en omslachtig.

[ Voor 23% gewijzigd door RobIII op 25-01-2013 15:41 ]

[Big Womly]

YellowOnline schreef op donderdag 24 januari 2013 @ 16:30:
Dat is wat ik doe met dit idee, nee?

C#:

If ($SearchString.Contains"'"){$SearchString.Replace("'","''"}

Niet echt, nee. Dit is een andere oplossing voor het single quote issue, maar je gaat nog steeds eerst je volledige query builden en daarna deze compileren en uitvoeren.

Bij prepared statements ga je je query eerst compileren, daarna parameters toevoegen en dan pas uitvoeren.

SQL:

select * from tablename where columname = ?

_{Het kan zijn dat MySQL een ander symbool gebruikt voor je parameters dan de '?'}
Deze query wordt door je Query processor al gecompileerd. Daarna ga je je parameter invullen en kun je die tenslotte gaan uitvoeren. Geen behoefte meer aan single quotes om te zetten, je query is toch al gecompileerd. Wat je ook toevoegd als parameter, dat is waar columname met vergeleken zal worden.

Een ander leuk voordeel bij prepared statements is: je kan dezelfde query meerdere keren achter elkaar gebruiken door enkel de parameter te veranderen. De query zelf moet niet opnieuw gecompileerd worden maar kan rechtstreeks opnieuw uitgevoerd worden

[ Voor 7% gewijzigd door Big Womly op 25-01-2013 12:51 ]

[YellowOnline]

Ik heb wat zitten lezen ivm stored procedures en snap wel hoe dat werkt denk ik.

Oude code (Powershell, geen C#!)

C#:

#Originele code (simplified) die een error zal genereren door de apostrof
$SearchString = "Articulate Studio '09 Pro"
$SqlServer = "SQLCluster.yellow.local"
$SqlDBName = "SMS_Yellow"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlDBName; Integrated Security = True"
$SqlCmd.Connection = $SqlConnection
$SqlDataSet = New-Object System.Data.DataSet
$SqlQuery = "SELECT sys.Netbios_Name0, arp.InstallDate0, arp.DisplayName0 FROM v_R_System sys JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID JOIN v_FullCollectionMembership fcm ON sys.ResourceID=fcm.ResourceID WHERE arp.DisplayName0 LIKE '$SearchString' AND fcm.CollectionID='SMS00004'"
$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
$SqlAdapter.Fill($SqlDataSet) | Out-Null
$colComputers = $SqlDataSet.Tables[0]
$SqlConnection.Close()

Mijn eerste Stored Procedure (eens wat anders dan 'Hello World!')

SQL:

-- =============================================
-- Author:      YellowOnline
-- Create date: 25/01/2013
-- Description: Returns computers with a specific application installed and the install date

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetComputersWithSpecificSoftware 
    @SearchString nvarchar(50)= 0, 
    @CollectionID nvarchar(8) = 0
AS
BEGIN
    SET NOCOUNT ON;
    SELECT sys.Netbios_Name0, arp.InstallDate0, arp.DisplayName0 FROM v_R_System sys JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID JOIN v_FullCollectionMembership fcm ON sys.ResourceID=fcm.ResourceID WHERE arp.DisplayName0 LIKE @SearchString AND fcm.CollectionID=@CollectionID
END
GO

De nieuwe code:

C#:

#Nieuwe code (simplified)
$SearchString = "Articulate Studio '09 Pro"
$SqlServer = "SQLCluster.yellow.local"
$SqlDBName = "SMS_Yellow"
$CollectionID = "SMS00004"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlDBName; Integrated Security = True"
$SqlCmd = $SqlConnection.CreateCommand()
$SqlCmd.CommandText = "EXEC dbo.GetComputersWithSpecificSoftware  @SearchString, @CollectionID"
$SqlCmd.Parameters.AddWithValue("@SearchString", $SearchString) | Out-Null
$SqlCmd.Parameters.AddWithValue("@CollectionID", $CollectionID) | Out-Null
$SqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlCmd
$SqlDataSet = New-Object System.Data.Dataset
$SqlDataAdapter.Fill($SqlDataSet) | Out-Null
$colComputers = $SqlDataSet.Tables[0]
$SqlConnection.Close()

Mijn probleem is nu inderdaad opgelost

Een groot deel van voorgaande discussie heb ik trouwens niet helemaal begrepen - en was volgens de deelnemers ook offtopic - maar in hoeverre is dit systeem nu veilig? Veiligheid was niet waar mijn probleem over ging, maar nu we nu toch bezig zijn...

[RobIII]

Veiligheid was wél (indirect weliswaar) waar het over ging. Beide (je probleem én veiligheid) hebben namelijk te maken met (fatsoenlijke) escaping. Dat is de crux.

Waarom ben je nu gegaan voor Stored Procedures i.p.v. Prepared statements? Beide kan, er leiden meer wegen naar Rome, maar ik vraag me wel af waarom je het één boven het ander verkozen hebt terwijl de nadruk hier toch wel een beetje op prepared statements lag. Gewoon uit nieuwgierigheid. Er zijn namelijk wél verschillen en bep. consequenties verbonden aan elk van de keuzes en ik vraag me af of je die afgewogen hebt.

[ZaZ]

Ik zou gewoon voor prepared statements gaan. Een SP is imho meer voor als er ook logic bij komt kijken

[YellowOnline]

RobIII schreef op vrijdag 25 januari 2013 @ 12:14:
Veiligheid was wél (indirect weliswaar) waar het over ging. Beide (je probleem én veiligheid) hebben namelijk te maken met (fatsoenlijke) escaping. Dat is de crux.

Waarom ben je nu gegaan voor Stored Procedures i.p.v. Prepared statements? Beide kan, er leiden meer wegen naar Rome, maar ik vraag me wel af waarom je het één boven het ander verkozen hebt terwijl de nadruk hier toch wel een beetje op prepared statements lag. Gewoon uit nieuwgierigheid. Er zijn namelijk wél verschillen en bep. consequenties verbonden aan elk van de keuzes en ik vraag me af of je die afgewogen hebt.

Omdat ik daar meer info over vond op het eerste zicht. Echt afwegen is moeilijk in een wereld waar ik helemaal niet in thuis ben (SQL). Maar ik ga straks - ben eerst met een support call van MS voor iets anders bezig - naar prepared statements kijken, want ik heb tijdens de lunchbreak gehoord dat ik mijn support van Microsoft waarschijnlijk kwijt ben als ik die stored procedure niet snel doe verdwijnen. We mogen niet aan de DB komen van SCCM, zo blijkt, en een stored procedure - al lijkt dat mij onschuldig, vooral omdat het een zuivere leesopdracht is - wordt ook beschouwd als 'aan de DB' komen.

Ik hoop nu wel dat prepared statements aan de kant van mijn coding zitten en niet ook aan de kant van de DB...

[ Voor 5% gewijzigd door YellowOnline op 25-01-2013 13:18 ]

[RobIII]

Of je support verliest weet ik niet, maar met parameterized queries (a.k.a. Prepared statements) zitten wel "aan de goede kant"

[YellowOnline]

RobIII schreef op vrijdag 25 januari 2013 @ 13:26:
Of je support verliest weet ik niet, maar met parameterized queries (a.k.a. Prepared statements) zitten wel "aan de goede kant"

Inderdaad: 't is eigenlijk nagenoeg hetzelfde, maar dan aan mijn kant. Ik vond geen Powershell voorbeeld, maar toen ik het C# voorbeeld zag op Wikipedia kwam me dat nogal bekend voor, dus heb ik dezelfde logica even in PoSh toegepast:

C#:

$SearchString = "Articulate Studio '09 Pro"
$SqlServer = "SQLCluster.yellow.local"
$SqlDBName = "SMS_Yellow"
$CollectionID = "SMS00004"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlDBName; Integrated Security = True"
$SqlCmd = $SqlConnection.CreateCommand()
$SqlCmd.CommandText = "SELECT sys.Netbios_Name0, arp.InstallDate0, arp.DisplayName0 FROM v_R_System sys JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID JOIN v_FullCollectionMembership fcm ON sys.ResourceID=fcm.ResourceID WHERE arp.DisplayName0 LIKE @SearchString AND fcm.CollectionID=@CollectionID"
$SqlCmd.Parameters.AddWithValue("@SearchString", $SearchString) | Out-Null
$SqlCmd.Parameters.AddWithValue("@CollectionID", $CollectionID) | Out-Null
$SqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlCmd
$SqlDataSet = New-Object System.Data.Dataset
$SqlDataAdapter.Fill($SqlDataSet) | Out-Null
$colComputers = $SqlDataSet.Tables[0]
$SqlConnection.Close()

Perfecte oplossing voor mij: lost speciale tekens op, veilig voor SQL injection en niet aan de DB kant. Dank aan allen, ik heb veel over SQL bijgeleerd!

[Gomez12]

YellowOnline schreef op vrijdag 25 januari 2013 @ 13:17:
[...]
Omdat ik daar meer info over vond op het eerste zicht. Echt afwegen is moeilijk in een wereld waar ik helemaal niet in thuis ben (SQL). Maar ik ga straks - ben eerst met een support call van MS voor iets anders bezig - naar prepared statements kijken, want ik heb tijdens de lunchbreak gehoord dat ik mijn support van Microsoft waarschijnlijk kwijt ben als ik die stored procedure niet snel doe verdwijnen. We mogen niet aan de DB komen van SCCM, zo blijkt, en een stored procedure - al lijkt dat mij onschuldig, vooral omdat het een zuivere leesopdracht is - wordt ook beschouwd als 'aan de DB' komen.

Wederom offtopic

Maar hoe is het geregeld dat jij zonder SQL-expertise, niet enkel op een read-only variant van die dbase zit? Helemaal als er je support er ook door getroffen kan worden.

[YellowOnline]

Gomez12 schreef op vrijdag 25 januari 2013 @ 15:29:
[...]

Wederom offtopic

Maar hoe is het geregeld dat jij zonder SQL-expertise, niet enkel op een read-only variant van die dbase zit? Helemaal als er je support er ook door getroffen kan worden.

offtopic:
Naast Powershell-scripter ben ik de admin van de System Center producten en daardoor ook diegene die de SQLs geïnstalleerd heeft. Mijn account is inderdaad overpowered, maar dat is omdat ik een hele hoop jobs combineer. Ik ben wel geen domain admin trouwens

Maar denk niet dat ik al die rechten voor de fun heb: dat is een gevolg van zwaar onderbemand te zijn. Een handvol SC producten beheren voor pakweg 25 000 gebruikers op je eentje is not fun. Daarnaast schrijf ik nog scripts voor al die producten én voor het serverteam (bv. serverinstallaties automatiseren van A-Z).

[ Voor 21% gewijzigd door YellowOnline op 25-01-2013 15:41 ]