[SQL Server] Stored Procedure troubles*

[ronjon]

Ik heb een stored procedure gemaakt die uit verschillende tabellen dmv een query adres gegevens ophaalt

code:

CREATE PROCEDURE dbo.printpage
@plaats varchar(255)
AS
SELECT     dbo.tbl_Bedrijf.bedrijf_ID AS bedrijfid, dbo.tbl_Bedrijf.bedrijf_Naam AS naam, dbo.tbl_Bedrijf.bedrijf_StraatNaam AS straat, 
                      dbo.tbl_Bedrijf.bedrijf_HuisNummer AS nummer, dbo.tbl_Bedrijf.bedrijf_Postcode AS pc, dbo.tbl_Bedrijf.bedrijf_Plaats AS plaats, 
                      dbo.tbl_Bedrijf.bedrijf_TelefoonNummer AS telefoon, dbo.tbl_Bedrijf.bedrijf_FaxNummer AS fax, dbo.tbl_Bedrijf.bedrijf_EmailAdres AS email, dbo.tbl_Tarief.tarief_M1 AS tarm1, 
                      dbo.tbl_Tarief.tarief_Overdekt AS taroverdekt, dbo.tbl_Tarief.tarief_Buiten AS tarbuiten, dbo.tbl_Tarief.tarief_M2 AS tarm2, 
                      dbo.tbl_Tarief.tarief_Ophaal AS tarophaal, dbo.tbl_Aanbod.aanbod_PlaatsOverdekt AS aantalover, 
                      dbo.tbl_Aanbod.aanbod_PlaatsBuiten AS aantalbuiten, dbo.tbl_Tekst.tekst_Inhoud AS tekst
FROM         dbo.tbl_Bedrijf LEFT OUTER JOIN
                      dbo.tbl_Tarief ON dbo.tbl_Bedrijf.bedrijf_ID = dbo.tbl_Tarief.bedrijf_ID LEFT OUTER JOIN
                      dbo.tbl_Aanbod ON dbo.tbl_Bedrijf.bedrijf_ID = dbo.tbl_Aanbod.bedrijf_ID LEFT OUTER JOIN
                      dbo.tbl_Tekst ON dbo.tbl_Bedrijf.bedrijf_ID = dbo.tbl_Tekst.bedrijf_ID
WHERE     (dbo.tbl_Bedrijf.bedrijf_Plaats = @plaats)
ORDER BY dbo.tbl_Bedrijf.bedrijf_Naam
GO

op het moment dat mijn variable @plaats een plaatsnaam is zonder spatie's (amsterdam) wordt de procedure goed uitgevoerd. Op het moment dat er een spatie in de procedure zit (den haag) krijg ik een foutmelding :
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'Haag'.

[Verwijderd]

Probeer eens van de variabele tussen enkele quotes te plaatsen, zoals 'Den Haag'

[Verwijderd]

zet er eens neer '@plaats'

[Feyd-Rautha]

Wanneer @plaats vervangen wordt door een string met spaties wordt uw WHERE-clause:

code:

WHERE bedrijf_plaats = bladie blaat

die bladie blaat moet tussen quotes zodat dat als 1 string aanzien wordt

[ Voor 41% gewijzigd door Feyd-Rautha op 26-05-2003 13:28 ]

[ronjon]

in mijn ASP bleek dit de oplossing te zijn:

code:

plaats = Chr(39) & Session("plaats") & chr(39)
sSql = "printpage " & plaats

waarbij Chr(39) dus de single quote voorstelt

[EfBe]

sinds wanneer vervang je parameters met stringetjes?

[_Thanatos_]

het zat em dus in de aanroep van de stored proc en niet in de uitvoer ervan. De TS geeft de oplossing al. je roept nu dus

SQL:

EXEC printpage 'blaat'

aan, in plaats van

SQL:

EXEC printpage blaat

Ohja, TS, als ik jou was zou ik er gewoon

ASP:

plaats = "'" & Replace(Session("plaats"), "'", "''") & "'"

van maken. Staat wat netter en voorkomt malafide code in je SQL statement.

[whoami]

Normaal gezien kan dat van die quotes het probleem niet zijn.

Aangezien @plaats van het type varchar is, zal het DBMS zelf zorgen voor de quotes.


Ach, idd. Als je je parameter niet meegeeft als een variable van het type string, of als je die quotes zelf niet zet in je ASP code, dan krijg je idd een probleem.

Echter, in je SP moet je je geen zorgen maken over quotes ed.

[ Voor 39% gewijzigd door whoami op 26-05-2003 14:16 ]

[whoami]

_Thanatos_ schreef op 26 mei 2003 @ 14:13:

Ohja, TS, als ik jou was zou ik er gewoon

ASP:

plaats = "'" & Replace(Session("plaats"), "'", "''") & "'"

van maken. Staat wat netter en voorkomt malafide code in je SQL statement.

Ik vermoed dat, als je SP's gebruikt, je je daar allemaal geen zorgen over hoeft te maken. Het DBMS zorgt er wel voor dat alles goed gaat.

[_Thanatos_]

whoami, in een van de eerste security-lessen krijg je dit juist voor je kiezen... wat als de Session("plaats") nou eens "'\nDROP DATABASE [grote bedrijfskritische db]\n--" bevat?

[whoami]

Test het eens uit als je stored procedures gebruikt.

[whoami]

Ik heb het net ff uitgeprobeerd.

Ik heb volgende stored procedure:

code:

CREATE PROCEDURE INSERT_SOMETHING (@p_Naam   VARCHAR(50))
AS
BEGIN

   INSERT INTO tblContact (naam) VALUES (@p_Naam);

END

Deze wordt door volgende code opgeroepen:

code:

SqlCommand cmd = new SqlCommand (conn);
cmd.CommandText = "INSERT_SOMETHING";
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add (@p_Naam, SqlDbType.Varchar);

cmd.Parameters["@p_Naam"].Value = txtNaam.Text;

cmd.ExecuteNonQuery();

Ik volgende waardes ingegeven in txtNaam:

"Dit is een naam"
1;DELETE FROM tblContact;

etc.... en ik ben niks van m'n data kwijt, de records zijn ook mooi toegevoegd zoals ik dat wenste.

Als je echter je queries dynamisch opbouwt in je code dmv concatenatie, en je ook geen parameters gebruikt, dan moet je idd oppassen, en de user-input controleren.
Echter, door het gebruik van parameters:

code:

SELECT * FROM tabel WHERE naam = @p_naam;

_bv
kan je al heel wat problemen voorkomen.

[ Voor 18% gewijzigd door whoami op 26-05-2003 20:52 ]

[_Thanatos_]

ok, you've got me

maar dan nog, de manier die jij omschrijft is wel de meest omslachtige. Het dynamisch opbouwen van een string met een EXEC statement is gewoon sneller en eenvoudiger. Je moet dan dus alleen de de enkele quotes verdubbelen.

[whoami]

_Thanatos_ schreef op 26 May 2003 @ 21:29:
ok, you got, me

maar dan nog, de manier die jij omschrijft is wel de meest omslachtige. Het dynamisch opbouwen van een string met een EXEC statement is gewoon sneller en eenvoudiger. Je moet dan dus alleen de de enkele quotes verdubbelen.

Omslachtig?
Net niet dacht ik. Je hoeft helemaal je input niet te controleren, terwijl jij dat met die dynamische queries wel moet doen. Het DBMS zorgt voor alles.
Trouwens, stored procedures zijn de snelste manier om gegevens uit een DB te halen of gegevens erin te stoppen.
Stored Procedures worden nl. bij creatie gecompileerd, en het DBMS bewaart het snelste execution plan om de SP uit te voeren als ze aangeroepen wordt.

Gebruik maken van Stored Procedures is gewoon de netste manier. Je laat de database doen waarvoor ze gemaakt is, en je kunt netjes n-tier ontwikkelen.

Zie ook dit topic:
[rml][ SQL] Query in database/Nut van Stored Procedures*[/rml]

[ Voor 5% gewijzigd door whoami op 26-05-2003 21:33 ]

[Annie]

whoami schreef op 26 May 2003 @ 20:51:
Als je echter je queries dynamisch opbouwt in je code dmv concatenatie, en je ook geen parameters gebruikt, dan moet je idd oppassen, en de user-input controleren.
Echter, door het gebruik van parameters:

code:

SELECT * FROM tabel WHERE naam = @p_naam;

_bv
kan je al heel wat problemen voorkomen.

Je hebt gelijk, alleen gebruikt de TS geen command object om z'n sproc uit te voeren, maar een simpele

ASP:

conn.execute("stored_procedure " & parameter)

en dan zit je dus wel met een probleem als je de invoer niet controleert. Wel of geen parameters maakt dan niet veel meer uit.

[EfBe]

Precies. Ik ging er ook vanuit dat hij een command object gebruikte, waarom zou je anders parameters gebruiken.

SqlInjection voorkomen: strippen van '--', " ' ", ';' '@'.

[_Thanatos_]

SqlInjection voorkomen: strippen van '--', " ' ", ';' '@'.

Mwa, enkele quotes in 2 enkele quotes omzetten is genoeg. "strippen" betekent het weghalen van die karakters, tenzij je nog nooit een stripper hebt gezien

Als je mijn manier gebruikt krijg je bijvoorbeeld zo'n string:

SQL:

'blaat ''--@; doe iets'

en heus, die kan geen kwaad. De enkele quotes hebben nagenoeg de hoogste precedence.

Magoed, wel altijd je waarden tussen enkele quotes zetten dan

[EfBe]

zet jij numerieke waarden tussen quotes?

[_Thanatos_]

Nee, die cast ik met CLng()
Dan kunnen er alleen maar getallen in voorkomen

[EfBe]

_Thanatos_ schreef op 27 May 2003 @ 12:23:
Nee, die cast ik met CLng()
Dan kunnen er alleen maar getallen in voorkomen

En de error die vang je op middels On Error Resume Next? (het enige wat mogelijk is in asp)

1 method maken die je include die een command object vult en je bent klaar. Nooit meer gepruts met het strippen van parametervalues, want dat is niet meer nodig.