Eesti isikukoodi kontrollsumma arvutamise funktsioon Transact-SQL

-- =============================================
-- Author: Kuido Külm
-- Description: Arvutab Eesti isikukoodi kontrollsumma
-- =============================================
CREATE FUNCTION [dbo].[EST_PERSON_IDCODE_CHECKSUM_S](@isikukood NVARCHAR(11))
RETURNS NVARCHAR(1)
AS
BEGIN
DECLARE  @checksum INT = -1, @sum INT=0, @weight INT=1, @i INT=1,@k INT
WHILE @i <= 10
BEGIN
    SET @k=CAST(SUBSTRING(@isikukood,@i,1) AS INT)*@weight
SET @sum=@sum+@k
IF @weight = 9
 SET @weight=1
ELSE
 SET @weight=@weight+1
SET @i=@i+1
END
SET @checksum=@sum%11
IF @checksum != 10
  RETURN CAST(@checksum AS NVARCHAR(1))

SET @sum=0
SET @weight=3
SET @i=1
WHILE @i <= 10
BEGIN
    SET @k=CAST(SUBSTRING(@isikukood,@i,1) AS INT)*@weight
SET @sum=@sum+@k
IF @weight = 9
 SET @weight=1
ELSE
 SET @weight=@weight+1
SET @i=@i+1
END
SET @checksum=@sum%11
IF @checksum != 10
  RETURN CAST(@checksum AS NVARCHAR(1))

RETURN '0'
END

TableAdapteri CommandTimeouti muutmine

Mõnikord juhtub, et andmebaasis pikk päring ja vaja CommandTimeouti pikemaks panna, et veebiserver ära ei katkestaks, seda saab teha partial klassiga

PAST_DUE_ACCOUNTS_S on TableAdapteri klassi nimi millele tuleb uus klass lisada

public partial class PAST_DUE_ACCOUNTS_STableAdapter
{
        public void SetCommandTimeout(int timeout)
        {
        foreach (IDbCommand command in CommandCollection)
        command.CommandTimeout = timeout;
        }
}

Reavahetuse näitamine SQL SERVER andmeväljast asp:Label-is

Veebivormi kaudu tekstiboksi sisse reavahetusi sisestades võib tekkida soov pärast need reavahetused säilitada veebis taaskuvamisel

MS SQL SERVER säilitab andmeväljas reavahetusi kui CHAR(10) sümbolit (\n). Andmebaasi väljast saab reavahetust leida PATINDEX('%'+CHAR(10)+'%', ...) funktsiooni abil

SELECT [message_text]
      ,PATINDEX('%'+CHAR(10)+'%',message_text) AS esimene_reavahetus
  FROM [dbo].[MESSAGES]

Kui nüüd vaja reavahetust ka HTML koodina näidata või teha seda nii, et asendad andmebaasi CHAR(10) väärtuse HTML reavahe märgendiga <br />

näiteks kasutab asp:Label mille paneb veel asp:Panel sisse kerimisribade pärast

<asp:Panel runat="server" ID="PanelSisu" Height="250px" ScrollBars="Vertical">
              <asp:Label ID="tbMessageBody" runat="server" ReadOnly="True"></asp:Label>
</asp:Panel> 

Miskipärast aga iga kord SQL SERVER-i CHAR(10) ja C# \n omavahel hästi ei ühildu.

Reeglina peaks töötama järgmine konstruktsioon

string msd = table.Rows[0]["message_text"].ToString();
msd = msd.Replace(Environment.NewLine, "<br />");

aga IIS6 miskipärast ei tööta. Üks mitte eriti ilus lahendus on see, et proovib erinevad variandid läbi. Sobib siis kui regulaaravaldsed pole just tugevaim külg.

string msd = table.Rows[0]["message_text"].ToString();
Regex regex = new Regex(@"(\r\n|\r|\n|\n\r)+"); //IIS6 peale ei tööta, IIS8 töötab
msd = regex.Replace(msd, "<br />");
msd = msd.Replace(Environment.NewLine, "<br />"); //see newline mõnikord ei tööta hästi IIS6
msd = Regex.Replace(msd, @"\r\n?|\n", "<br />");  //IIS6 peale töötab

Viisakas on muidugi väljund ennem AntiXSS teegi funktsioonist GetSafeHtmlFragment läbi lasta.
GetSafeHtmlFragment aga sööb reavahe <br /> märgendi ära.

Üle nurga lahendus võib olla selline, et pöörab reavahe mingiks vähetõenäolise stringijadaks. Näiteks  "#####" ja peale GetSafeHtmlFragment funktsiooni asendab "#####" "<br />" märgendiga

string msd = table.Rows[0]["message_text"].ToString();
Regex regex = new Regex(@"(\r\n|\r|\n|\n\r)+");
msd = regex.Replace(msd, "#####");
msd = msd.Replace(Environment.NewLine, "#####"); //see newline mõnikord ei tööta hästi IIS6
msd = Regex.Replace(msd, @"\r\n?|\n", "#####");  //IIS6 peale töötab
this.tbMessageBody.Text = Microsoft.Security.Application.Sanitizer.GetSafeHtmlFragment(msd).Replace("#####", "<br />");

MERGE käsuga puuduvate ridade lisamine tabelmuutujasse

On selline tabelmuutuja
DECLARE @tulem TABLE ( WORKER NVARCHAR(40) COLLATE DATABASE_DEFAULT, HOUSES_GROUPS_ID INT, TOTAL INT DEFAULT 0) ja esialgne päring annab tabeli sisuks

aga vaja näidata ka puuduolevaid 0 ridu, ehk saada selline tulemus

ehk lisame KAMPUS_OLLE-le puudu oleva HOUSES_GROUPS_ID 42 korral koguse 0

MERGE lause võib olla selline:


MERGE @tulem AS Target
    USING ( SELECT DISTINCT WK.WORKER, HG.HOUSES_GROUPS_ID FROM @tulem WK CROSS APPLY ( SELECT DISTINCT HOUSES_GROUPS_ID    FROM @tulem ) AS HG
       ) AS Source (WORKER, HOUSES_GROUPS_ID) ON (Target.HOUSES_GROUPS_ID = Source.HOUSES_GROUPS_ID AND Target.WORKER = Source.WORKER)
        WHEN NOT MATCHED BY TARGET THEN
            INSERT (HOUSES_GROUPS_ID, WORKER) VALUES (Source.HOUSES_GROUPS_ID, Source.WORKER);


Sisemine päring MERGE lauses:
SELECT DISTINCT WK.WORKER, HG.HOUSES_GROUPS_ID FROM @tulem WK CROSS APPLY ( SELECT DISTINCT HOUSES_GROUPS_ID FROM @tulem ) AS HG

annab WORKER ja HOUSES_GROUPS_ID ristkorrutise

ja sealt mestib WHEN NOT MATCHED puuduolevad lisaks algsesse tabelisse.
Kuna @tulem TOTAL INT DEFAULT 0 vaikeväärtus on 0 pannakse see ka vaikimisi puuduvatele väärtuseks

SQL SERVER SELECT päringu parametriseeritud järjestamine erinevate andmetüüpide korral

Andmebaasi tabelis on väli text_id INT tüüpi ja title_text NVARCHAR(1000).
Kui nüüd vaja salvestatud protseduuris parameetrina ette anda mis järjestuses andmeid kätte saada tahetakse


ALTER PROCEDURE [dbo].[TEXT_ROLES_KUIDO_S]
    @sortorder TINYINT=0 --0 NIME JÄRGI 1-ID JÄRGI
AS
BEGIN
SET NOCOUNT ON
   SELECT [text_id],[title_text] FROM [dbo].[TEXT_TITLES]
            ORDER BY CASE WHEN @sortorder = 0 THEN title_text ELSE text_id END
END

siis selline lähenemine annab veateate @sortorder = 0 korral

Conversion failed when converting the nvarchar value 'Mingi tekst' to data type int.

kuna title_text-i hakatake INT andmetüübiks pöörama. Veast saab lahti, kui ORDER BY kirjutada järgmiselt

   SELECT [text_id],[title_text] FROM [dbo].[TEXT_TITLES]
      ORDER BY CASE WHEN @sortorder = 0 THEN title_text END ,
                     CASE WHEN @sortorder = 1 THEN text_id END

SQL SERVER XML andmetüübi pööramine VARCHAR(MAX)

Kui XML andmetüübi pööramisel VARCHAR(MAX) võib hakata pilduma viga

Conversion of one or more characters from XML to target collation impossible

DECLARE @tulem VARCHAR(MAX), @xmlMuutuja
SET @xmlMuutuja= ... Mingi XML tüüpi väärtus

Viga ilmneb järgneval tüübiteisendamisel
SET @tulem=CAST(@xmlMuutuja AS VARCHAR(MAX))

siis veast saab lahti kui kõigepealt pöörata NVARCHAR(MAX) ja peale seda VARCHAR(MAX)

SET @tulem=CAST(CAST(@xmlMuutuja AS NVARCHAR(MAX)) AS VARCHAR(MAX))

Andmebaasi ühel väljal andmete uuendamise keelamise käsu süntaks

Vaja keelata, et üks andmebaasi roll ei saa muuta tabeli ühte välja
Süntaks siis selline:

DENY UPDATE(COMMENT) ON OBJECT::DBO.APPLICATION TO TENANT

Keelab andmete muutmise (UPDATE) tabeli APPLICATION väljal COMMENT andmebaasi rollile TENANT

Trigeri pinu

Mõnikord vaja teada, mis protseduur või SQL lausend põhjustab trigeri käivitamise
Trigeri pinu(trigger call-stack) teadasaamiseks saab kasutada DBCC INPUTBUFFER funktsiooni.

DML trigeri koodinäide

-- Püüame objekti ID kinni, kust trigeri väljakutse tehti DBCC INPUTBUFFER funktsiooni jaoks
DECLARE @temp2 NVARCHAR(4000), @temp NVARCHAR(MAX), @reqid INT
SET @reqid = ( SELECT request_id FROM sys.dm_exec_requests WITH(NOLOCK) WHERE session_id = @@SPID)
IF ISNULL(@reqid,0) = 0
SET @reqid = ( SELECT session_id FROM sys.dm_exec_requests WITH(NOLOCK) WHERE session_id = @@SPID)

--salvestame DBCC INPUTBUFFER funktsiooni väljakutsuva call stacki tabelmuutujasse
DECLARE @calls TABLE (EventType NVARCHAR(30) COLLATE DATABASE_DEFAULT NULL, Parameters SMALLINT NULL, EventInfo NVARCHAR(4000) COLLATE DATABASE_DEFAULT NULL)
DECLARE @sl NVARCHAR(4000)
SET @sl='DBCC INPUTBUFFER('+CAST(@reqid AS NVARCHAR(20))+') WITH NO_INFOMSGS'
--käivitab trigeris DBCC INPUTBUFFER funktsiooni
INSERT INTO @calls EXEC(@sl)

-- Teeme nüüd pinust väljavõtte
SET @temp2 = NULL
SELECT @temp2 = COALESCE(@temp2+',','') + EventType+' '+EventInfo FROM @calls

@temp2 muutujas on trigeri pinu olemas

https päring SQL serveri CLR protseduurist

Igasugu asju võimaldatakse ka andmebaasis teha CLR-iga aga kui vaja teha sertifikaadikindel https postitus võib seda teha nii.

Et serveri sertifikaatide veateadetest lahti saada tuleb kasutada
ServicePointManager.ServerCertificateValidationCallback meetodi ülekirjutamist

using System.Security.Cryptography.X509Certificates;
using System.Net.Security;
using System.Net;
using System.IO;


private static bool ValidateRemoteCertificate( object sender, X509Certificate certificate, X509Chain chain, SslPolicyErrors policyErrors )
{
//siia võib mingi mõistliku veatöötluse juurde arendada, praegu annab alati true, ehk kõikidest vigadest läheb mööda.
return true;
}

CLR protseduur ise

[Microsoft.SqlServer.Server.SqlProcedure]
public static void VeebiParing(SqlString url, SqlString andmed, SqlString kellelt, out SqlString outt)
{
try
{
//see on sertifikaadi veast möödahiilimiseks
ServicePointManager.ServerCertificateValidationCallback += new RemoteCertificateValidationCallback(ValidateRemoteCertificate);

HttpWebRequest myRequest=(HttpWebRequest)System.Net.WebRequest.Create((string)url);

myRequest.Method = "POST";
myRequest.ContentType = "application/x-www-form-urlencoded";


//paneme sisu kokku
StringBuilder postData = new StringBuilder();
//siin paneb andmed külge
postData.Append("Saadame="+(string)kellelt+"&andmeid="+(string)andmed);


//päringu andmete sisu tuleb läbi System.Uri.EscapeUriString lasta, muidu ei lähe läbi veebi.
string data = System.Text.Encoding.GetEncoding("UTF-8").GetBytes(System.Uri.EscapeUriString(postData.ToString()));
myRequest.ContentLength = data.Length;

// Striim veebi saatmiseks

Stream newStream = myRequest.GetRequestStream();
// Saadame minema
newStream.Write(data, 0, data.Length);
newStream.Close();

//loeme vastuse

HttpWebResponse loWebResponse = (HttpWebResponse)myRequest.GetResponse();

Encoding enc = System.Text.Encoding.GetEncoding("UTF-8");
StreamReader loResponseStream = new StreamReader(loWebResponse.GetResponseStream(), enc);
//kuna on protseduur siis nii saab sisulistvastust tagastada
outt = loResponseStream.ReadToEnd();
loResponseStream.Close();
loWebResponse.Close();

}
catch ( WebException ex)
{
outt = "WebException: " + ex.Message;
}
catch (SystemException ex)
{
outt="Error: "+ex.Message;
}
return;

}

Kui andmebaas hakkab CLR alusel protseduuri looma siis NVARCHAR(4000) sisendparameetri võid muuta NVARCHAR(MAX) peale ja ASSEMBLY tuleb teha UNSAFE märgendiga, mis annab CLR protseduurile turvalisuse seisukohalt laiad võimalused ehk kaaluda tasub muude võimaluste kasutamist

Mitte iga pakkfaili op.süsteemi käsurea käsku ei saa SQL SERVER-i tööna jooksutada

Vaja näiteks andmebaasi tööna käima tõmmata pakkfail.
Seadistab SQL Server Agent-i alt töö ära Type = Operating System (CmdExec) ja tõmbab pakkfaili sisu Command aknasse sisse (asja mõte kustutada D:\TEMP kataloogist kõid PDF ja DOC failid)

d:
cd \temp
del *.pdf
del *.doc

aga töö jooksutamisel tuleb selline viga ette

The process could not be created for step 1 of job 0x... (reason: 5). The step failed.

Kui nüüd teha SQL Server Agent-ile proxy ja mandaat ja käivitada töö sobiva kasutaja õigusega saad ikka sama vea

Häda selles, et Server Agentile kõik käsurea asjad ka seeditavad pole, ehk

d:

mis muidu vahetab käsureal kettaseadet aga SQL Server Agentile kohe mitte ei meeldi ja veateadet pillubki

Lahendus 1 (kustuta otse kataloogi nime ette andes)
del d:\temp\*.pdf
del d:\temp\*.doc


Lahendus 2 (tee kettaseadme vahetus cd käsuga)
cd d:
cd \temp
del *.pdf
del *.doc

DBCC FREEPROCCACHE kaudu läbi ADO.NET kaudu salvestatud protseduuri jooksutamisele vungi sisseandmine

Kui selline juhtum, et tõmbad salvestautd protseduuri Management Studio kaudu käima töötab asi kiiresti. Sama asi aga ASP.NET rakenduses läbi ADO.NET välja kutsudes jube aeglane, põhimõtteliselt ei töötagi siis saab asjale vunki juurde anda, kui
MS SQL SERVER-is korra protseduuride vahemälu tühjaks tõmmata

DBCC FREEPROCCACHE

Hoiatatakse küll, et ärge kasutage aga lõpptulemusena saad ka läbi ADO.NET välja kutsutud protseduuri kiiresti käima.

Soovitatav lahendus aga sellistel juhtumistel on SP poolt parameetrite arvamine ära lõpetada, ehk teisenda protseduuri parameetrid lokaalseteteks muutujateks

CREATE PROCEDURE ParameetriArvamine
@nimetus NVARCHAR(10)
WITH RECOMPILE
AS
DECLARE @nimetus1 NVARCHAR(10)

--teeme parameetri teisendamise, et SQL SERVER oskaks õiget käivitusplaani teha

SET @nimetus1 = @nimetus
SELECT id, nimi FROM SinuTabel WHERE nimi = @nimetus1

Kustutamine tabelmuutujast SELF-CORRELATED päringuga

Süntaks selline, et viitamisel tuleb tabelmuutuja nimi panna [ ] kandiliste sulgude sisse

DELETE FROM @lepingud WHERE EXISTS
( SELECT OP_LOG.CONTRACTS_ID FROM dbo.OP_LOG
WHERE OP_LOG.TEXT_TITLES_ID = 49 AND OP_LOG.CONTRACTS_ID = [@lepingud].CONTRACTS_ID )

LinkedServeri konfigureerimine ilma IP aadressita

Kui kaks SQL SERVER-it vaja omavahel kokku panna siis saab Linked Serveri teha IP aadressi põhiselt. Management Studio alt Server Objects -> Linked Servers -> New ja LinkedServeri nimeks paned IP aadressi.

Aga saab ka nii, et IP aadressi asemel võid kasutada näiteks nime, hea siis kui IP aadress peaks muutuma ei pea koodis tabelite juurde pöördumisi ümber kirjutama.

Mudida tuleb Windows Serveril hosts faili mis asub tavaliselt C:\Windows\System32\drivers\etc
kataloogis. Sinna faili kirjutad rea juurde

ip-aadress Serveri nimi #kommentaar
xxx.129.109.306 TEINESERVER #see teine SQL server millega tahad ühenduse luua

ja kui linked serverit hakkad tegema siis IP aadressi asemel võid kasutada TEINESERVER nime

Otsi stringi SQL SERVER-i andmebaasist

Kui enam meeles pole, mida kuhugi sai andmebaasi pandud siis otsimisel abiks järgnev skript:

set nocount on

DECLARE @pikkus INT, @rowID INT, @maxRowID INT, @sql NVARCHAR(4000), @searchValue NVARCHAR(100)
SET @searchValue = 'ei tööta' --seda otsitakse

DECLARE @statements TABLE (rowID INT, SQLL NVARCHAR(MAX) COLLATE DATABASE_DEFAULT)
CREATE TABLE #results (tableName NVARCHAR(250) COLLATE DATABASE_DEFAULT, tableSchema NVARCHAR(250) COLLATE DATABASE_DEFAULT
, columnName NVARCHAR(250) COLLATE DATABASE_DEFAULT, foundtext NVARCHAR(MAX) COLLATE DATABASE_DEFAULT )
SET @rowID = 1
SET @pikkus=LEN(@searchValue)

--TEXT 35
--NTEXT 99
--VARCHAR 167
--CHAR 175
--NVARCHAR, SYSNAME 231
--NCHAR 239
--XML 241

--create CTE table holding metadata
;WITH MyInfo (tableName, tableSchema, columnName, XTYPE) AS (
SELECT sysobjects.name AS tableName, USER_NAME(sysobjects.uid) AS tableSchema
, syscolumns.name AS columnName, syscolumns.XTYPE
FROM sysobjects WITH(NOLOCK) INNER JOIN syscolumns WITH(NOLOCK)
ON (sysobjects.id = syscolumns.id)
WHERE sysobjects.xtype = 'U' AND sysobjects.category=0
AND sysobjects.name <> 'sysdiagrams' --MSSQL diagramme ei vaata
AND syscolumns.XTYPE IN (35,99,167,175,231,239,214) AND syscolumns.prec >= @pikkus
)

INSERT INTO @statements
SELECT row_number() over (order by tableName, columnName) AS rowID, 'INSERT INTO #results SELECT '''+tableName+''', '''+tableSchema+''', '''+columnName+''', CAST('+columnName+' AS NVARCHAR(MAX)) FROM ['+tableSchema+'].['+tableName+'] WITH (NOLOCK) WHERE '+
CASE WHEN myInfo.XTYPE=241 --XML
THEN +'CONVERT(NVARCHAR(MAX),['+columnName+'])'
ELSE '['+columnName+']'
END+' LIKE ''%'+@searchValue+'%'''
FROM myInfo

SET @maxRowID = ( SELECT MAX(rowID) FROM @statements )
WHILE @rowID <= @maxRowID
BEGIN
SET @sql = (SELECT sqll FROM @statements WHERE rowID = @rowID )
EXEC sp_executeSQL @sql
SET @rowID = @rowID + 1
END

SELECT * FROM #results
drop table #results

SQLSERVER loginid nõrkade salakoodidega

Otsib välja need SQL SERVER-i loginid, mille salakood on etteantud nimekirjas või salakoodiks on kasutajatunnus või kasutajatunnus pööratult

Käivitage skript ning olge loov, julm ja metoodiline nõrkuste ravimisel. Ärge halastage kellelegi !!

USE [master]
DECLARE @WeakPwdList TABLE(WeakPwd NVARCHAR(255) COLLATE DATABASE_DEFAULT )
--Nõrkade salakoodide nimekiri
--@@Name on selleks, et testida kas salakoodiks on kasutajatunnus
INSERT INTO @WeakPwdList(WeakPwd)
SELECT ''
UNION SELECT '123'
UNION SELECT '1234'
UNION SELECT '12345'
UNION SELECT '123456'
UNION SELECT 'abc'
UNION SELECT 'abc123'
UNION SELECT 'qwerty'
UNION SELECT 'qwert'
UNION SELECT 'qwer'
UNION SELECT 'asdfg'
UNION SELECT 'asdf'
UNION SELECT 'asd'
UNION SELECT 'default'
UNION SELECT 'guest'
UNION SELECT '@@Name123'
UNION SELECT '@@Name12'
UNION SELECT '@@Name1'
UNION SELECT '@@Name'
UNION SELECT '@@Name@@Name'
UNION SELECT '@@Name1@@Name'
UNION SELECT '@@Name12@@Name'
UNION SELECT '@@Name123@@Name'
UNION SELECT 'admin'
UNION SELECT 'Administrator'
UNION SELECT 'admin123'
--SELECT * FROM @WeakPwdList
SELECT sql_logins.name AS [LoginName],
CASE
WHEN PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',REVERSE(sql_logins.name)),password_hash) = 0 THEN REPLACE(t2.WeakPwd,'@@Name',sql_logins.name)
ELSE REPLACE(t2.WeakPwd,'@@Name',REVERSE(sql_logins.name))
END AS [Password]
,sql_logins.default_database_name,sql_logins.is_policy_checked,sql_logins.is_expiration_checked,sql_logins.is_disabled
,(SELECT suser_sname(owner_sid) FROM sys.databases WHERE databases.name = sql_logins.default_database_name) AS database_owner
FROM sys.sql_logins INNER JOIN @WeakPwdList t2 ON (PWDCOMPARE(t2.WeakPwd, password_hash) = 1
OR PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',sql_logins.name),password_hash) = 1
OR PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',REVERSE(sql_logins.name)),password_hash) = 1 )
--WHERE sql_logins.is_disabled=0
ORDER BY sql_logins.name


SQL LOGIN-eid saab sundida jälgima Windows Serveri policy-t
http://technet.microsoft.com/en-us/library/cc875814.aspx

Kontrollige, et Windowsi policyt oleksid peale seatud
ja SQL SERVERIS kasutajate tegemisel või muutmisel CHECK_POLICY = ON

Kes, kuna, kuidas, milleks ja kust (IP aadressiga) on andmebaasi küljes

Abiks järgnev päring

SELECT dm_exec_sessions.session_id, dm_exec_sessions.status
, dm_exec_sessions.login_name, dm_exec_sessions.original_login_name
, dm_exec_connections.net_transport
, dm_exec_connections.connect_time
, dm_exec_sessions.login_time
, dm_exec_connections.protocol_type
, dm_exec_connections.client_net_address
, dm_exec_connections.client_tcp_port
, dm_exec_connections.auth_scheme
, dm_exec_sessions.HOST_NAME, dm_exec_sessions.NT_DOMAIN
, dm_exec_sessions.program_name, dm_exec_sessions.client_interface_name
, dm_exec_sessions.language, dm_exec_sessions.client_version
, dm_exec_requests.command, dm_exec_requests.wait_type, dm_exec_requests.wait_time, dm_exec_requests.wait_resource
FROM sys.dm_exec_sessions
JOIN sys.dm_exec_connections ON (dm_exec_sessions.session_id = dm_exec_connections.session_id )
INNER JOIN master..sysprocesses ON (dm_exec_sessions.session_id = sysprocesses.SPID)
LEFT JOIN sys.dm_exec_requests ON (dm_exec_sessions.session_id = dm_exec_requests.session_id)
WHERE DB_NAME(sysprocesses.dbid) = DB_NAME()
--AND dm_exec_sessions.status != 'sleeping'


dm_exec_sessions.session_id põhjal võib teha KILL sellele ühendusele

dm_exec_sessions.program_name saad seada Web.Config faili ConnectionStringis Application Name parameetriga niimoodi

connectionString="Data Source=SQL2008SERVER;Min Pool Size=2;Application Name=MINURAKENDUS; ...

II astme SQL süstimise tõkestamine reapiiranguga

Üks viis kuidas tõkestada SCRIPT, IFRAME, FORM, BODY ja muude murdskriptimisvõimaluste lisamist andmebaasi on panna string tüüpi väljade peale reapiirangud näiteks LIKE operandi kasutades

ALTER TABLE [dbo].[HD_PROBLEMS] WITH CHECK ADD CONSTRAINT [CK_HD_PROBLEMS_SQL_INJECTION] CHECK ((NOT [problemDescription] LIKE '%<_%')) Siin LIKE '%<_%' tähendab kõik stringid kus < järel on mingi character sümbol (ka tühik)


ALTER TABLE [dbo].[HD_PROBLEMS] WITH CHECK ADD CONSTRAINT [CK_HD_PROBLEMS_SQL_INJECTION] CHECK ((NOT [problemDescription] LIKE '%<[A-Z]%'))

Siin LIKE '%<[A-Z]%' kõik stringid kus < järel on mingi täht A kuni Z

ALTER TABLE [dbo].[HD_PROBLEMS] WITH CHECK ADD CONSTRAINT [CK_HD_PROBLEMS_SQL_INJECTION] CHECK ((NOT [problemDescription] LIKE '%<[ASIDBMFH/]%'))

Siin LIKE '%<[ASIDBMFH/]%' tähendab kõik stringid kus < järel on mingi sümbol nimekirjast ASIDBMFH/
Koos NOT-iga saame lausendi mis kontrollib "ei ole mustas nimekirjas".
Standardid soovitavad muidugi kasutada valge nimekirja kontrollimist.

Kuidas töötab

Kui nüüd üritatakse XSS-i kirjutada andmebaasi välja sisse (II astme rünnak)



siis reapiirang asub tegevusse ja andmeid muuta ei lubata

String tüüpi muutuja polsterdamine SQL süstimise vastu

Kui ei pääse üle ega ümber sellest, et vaja dünaamiline SQL kirjutada ja sp_executesql kasutada ei saa siis string tüüpi muutujad (VARCHAR, NVARCHAR, CHAR jne) polsterda järgmiselt:

REPLACE(@otsiparam,'''','''''') ning enne ja pärast seda veel kolmed ülakomad

näiteks niimoodi:

DECLARE @os NVARCHAR(MAX)
SET @otsiparam=LTRIM(RTRIM(ISNULL(@otsiparam,'')) --juhuks, kui tühikud ees ja taga võivad probleemiks olla ning parameeter võib ka NULL olla.
SET @os='SELECT TOP 1 NIMED.NIMI FROM DBO.NIMED WITH(NOLOCK) WHERE NIMED.ID = '''+REPLACE(@otsiparam,'''','''''')+''''
EXECUTE (@os)

Kui vaja ainult ühte rida andmebaasist kätte saada siis mõtekas TOP 1
SELECT lausesse lisada. Aitab OR 1=1 rünnakute vastu.

See muutuja, mida EXECUTE lausega käima tõmbad võiks olla NVARCHAR(MAX) tüüpi, siis ei jõuta seda ülakomasid ''''''' täis ajada, ennem astuvad võrgu piirangud vahele.

C# käib polsterdamine nii
public string Polsterda(string inputSQL)
{
return inputSQL.Replace("'", "''");
}

XML andmete näitamine TreeView ja XmlDataSourcega

Andmebaasis on XML väli kuhu paneb sisse igasugu andmeid, peamiselt logimisinfot mille jaoks iga kord tabelisse eraldi tulpa teha ei viitsi. XML andmete stuktuur kuidas kunagi
mõnel rohkem
mõnel vähem
ja tulevikus võib vajadusel juurde lisada.

Ekraanil hierarhiliste andmete näitamiseks sobib hästi TreeView nimeline control, XML andmed salvestab XmlDataSourceTähele peab panema, et XmlDataSource on EnableCaching vaikimisi True, kuna vaja andmebaasist dünaamiliselt iga kord uusi andmeid näidata tuleb
EnableCaching="false" seada

Vaikmisi TreeView seab andmed XML elementde külhge, et ekraanil näitab ainult
Options, AcceptedUser ja RELATED_CONTRACT teksti nii nagu XML andmete tippude nimed on, kuna see mis andmebaasi XML väljast tuleb võib igakord erinev olla siis
ontreenodedatabound="TreeViewExtraInfo_TreeNodeDataBound" eventis tuleb natuke käsitsitööd teha, näiteks nii:

protected void TreeViewExtraInfo_TreeNodeDataBound(object sender, TreeNodeEventArgs e)
{
try
{
System.Xml.XmlElement cdf = (System.Xml.XmlElement)e.Node.DataItem;

if (cdf.Name == "Options") //juurelement
{
e.Node.Text = Resources.Resource.ExtraInfo;
}
else
{
if (cdf.InnerXml != "")
{
try //kui ta sellist node nimetust ei leia resursi tekstidest, võtame NODE enda nime
{
e.Node.Text = HttpContext.GetGlobalResourceObject("Resource", cdf.Name).ToString()+ " " + cdf.InnerText;
}
catch (SystemException ex)
{
e.Node.Text = cdf.Name + " " + cdf.InnerText;
}
}
}
if (cdf.HasAttributes) //kui on atribuute siis lisame elemendile ühe alamtipu
{
for (int i = 0; i < cdf.Attributes.Count; i++)
{
TreeNode db;
try
{
//kas ressursi tekstides on tipu elemendi kirjeldus olemas
db = new TreeNode(HttpContext.GetGlobalResourceObject("Resource", cdf.Attributes[i].Name).ToString() + " " + cdf.Attributes[i].Value);
}
catch (SystemException ex)
{
db = new TreeNode(cdf.Attributes[i].Name + " " + cdf.Attributes[i].Value);
}
e.Node.ChildNodes.Add(db);
}
}
}
catch (SystemException ex)
{
this.CustomValidator1.ErrorMessage = ex.Message;
this.CustomValidator1.IsValid = false;
}
}

Ekraanile aga paistab:


AcceptedUseri all on atribuutidest tehtud lisatipud

Andmete külgepanemine käib XmlDataSourceExtraInfo.Data property kaudu
this.XmlDataSourceExtraInfo.Data = this.ApplicationOptions(this.application_id);
this.TreeViewExtraInfo.DataBind();

siin this.ApplicationOptions(this.application_id) tagastab XML stringi mille andmebaasi XML väljast loen

public string ApplicationOptions(int applicationId)
{
string retu = "";
using (SqlConnection konn = new SqlConnection(Configuration.ConnectionString))
{

SqlCommand komm = new SqlCommand("SELECT [dbo].[APPLICATION_OPTIONS_KUIDO_S](" + applicationId.ToString() + ")", konn);
komm.CommandType = CommandType.Text;
konn.Open();
Object ret = komm.ExecuteScalar();
if (!ret.Equals(DBNull.Value))
{
retu = ret.ToString();
}
konn.Close();
}
return retu;
}

BULK INSERT tekstifailist CLR-i kasutades

Sai kogemata SQLSERVER 2008 Web Edition maha müüdud ja siis avastatud, et Integration Services selle versiooni sees ei olegi. Kokkuvõttes tuleb asjad ise teha ja loeme nüüd BULK INSERT käsuga otse tekstifailist:


using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void HansaImport(SqlString fail, SqlString kataloog, SqlString sihttabel, SqlInt32 kustutafail)
{
//teeme sisendparameetrite kontrolli ja lõikame lõpud maha
string siht = Convert.ToString(sihttabel);
siht=siht.Substring(0, (siht.Length > 30 ? 30 : siht.Length));
string kat = Convert.ToString(kataloog);

kat = kat.Substring(0, (kat.Length > 30 ? 30 : kat.Length));
string fai = Convert.ToString(fail);
fai = fai.Substring(0, (fai.Length > 20 ? 20 : fai.Length));
string impafail = kat + fai;
if (System.IO.File.Exists(impafail))
{
using (SqlConnection konn = new SqlConnection("Context connection=true"))
{
SqlCommand komm = new SqlCommand("TRUNCATE TABLE " + siht + "; BULK INSERT " + siht + " FROM '" + impafail + "' WITH (FIELDTERMINATOR ='\t',ROWTERMINATOR ='\n', BATCHSIZE = 10000)", konn);
konn.Open();
komm.ExecuteNonQuery();
konn.Close();
if (kustutafail == 1)
{
System.IO.File.Delete(impafail);
}
}
}
else
{
throw new SystemException("CLR Error !! Missing file: " + fai + " in directory: " + kat);
}
}
}


Kui nüüd värk käivitada Management Studio alt siis kõik OK aga kui panna jooksma kui andmebaasi töö tuleb selline viga

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row

Tekstifailis on kuupäev kujul 23.10.2009

Et asi andmebaasi tööna jooksma saada anname temale kuupäeva formaadi ette
SET DATEFORMAT DMY
EXECUTE [dbo].[HansaImport]
@fail='arved.txt'
,@kataloog='D:\HANSA_IMPORT\'
,@sihttabel='[dbo].[HANSA_ARVED_TEMP]'
,@kustutafail=0

SQL SERVER Agent kasutab SQL SERVERi enda kuupäeva seadeid asjadest aru saamiseks