Lessons XI : Câu lệnh JOIN

Câu lệnh JOIN

Thứ ba, 09 Tháng 9 2008 11:08 blog.thegioiwebsite.net Hướng dẫn lập trình SQL – MySQL
Email In

Nối kết và khoá
Đôi khi chúng ta phải lấy dữ liệu từ hai bảng cùng một lúc, chúng ta thực hiện một kết nối.
Các bảng trong CSDL có thể quan hệ ràng buộc với nhau thông qua các khoá. Một khoá chính (primary key) là một cột mà trong đó mỗi giá trị của hàng phải là duy nhất. Mục đích của khoá là kết nối dữ liệu lại với nhau, từ nhiều bảng khác nhau mà không gây trùng lắp dữ liệu giữa các bảng.

Trong bảng Employees (nhân viên) ở ví dụ dưới đây có cột Employees_ID là khoá chính, bảo đảm rằng không thể có hai dòng nào có trùng Employees_ID. Employees_ID dùng để phân biệt hai nhân viên khi họ trùng tên.

Trong ví dụ dưới đây:

  • Employee_ID là khoá chính của bảng Employees.
  • Prod_ID là khoá chính của bảng Orders.
  • Cột Employeed_ID trong bảng Orders được sử dụng để kết nối với bảng Employees, chỉ đến nhân viên trong bảng Employees.

Bảng Employees:

Employees_ID Name
01 Hansen, Ola
02 Svendson, Tove
03 Svendson, Stephen
04 Pettersen, Kari

Bảng Orders:

Prod_ID Product Employee_ID
234 Printer 01
657 Table 03
865 Chair 03

Kết nối hai bảng với nhau

Chúng ta có thể lấy dữ liệu từ hai bảng bằng cách kết nối chúng, tương tự như sau:

Ví dụ: Tìm xem ai đã đặt hàng sản phẩm và họ đã đặt món hàng gì:

SELECT Employees.Name, Orders.Product FROM Employees, Orders WHERE Employees.Employee_ID = Orders.Employee_ID

kết quả trả về:

Name Product
Hansen, Ola Printer
Svendson, Stephen Table
Svendson, Stephen Chair

Ví dụ: Tìm xem ai đã đặt hàng máy in:

SELECT Employees.Name FROM Employees, Orders WHERE Employees.Employee_ID = Orders.Employee_ID AND Orders.Product = 'Printer'

kết quả trả về:

Name
Hansen, Ola

Sử dụng JOIN

Ta có thể sử dụng từ khoá JOIN để kết nối dữ liệu từ hai bảng.

Ví dụ: INNER JOIN

Cú pháp:

SELECT cột_1, cột_2, cột_3 FROM bảng_1 INNER JOIN bảng_2 ON bảng_1.khoá_chính = bảng_2.khoá_ngoại

Ai đã đặt hàng và họ đã đặt món hàng nào:

SELECT Employees.Name, Orders.Product FROM Employees INNER JOIN Orders ON Employees.Employee_ID = Orders.Employee_ID

INNER JOIN trả về tất cả các dòng ở cả hai bảng khi chúng tương ứng với nhau. Nếu có một dòng ở bảng Employees không ứng với dòng nào ở bảng Orders, dòng đó sẽ không được tính.

kết quả trả về:

Name Product
Hansen, Ola Printer
Svendson, Stephen Table
Svendson, Stephen Chair

Ví dụ: LEFT JOIN

Cú pháp:

SELECT cột_1, cột_2, cột_3 FROM bảng_1 LEFT JOIN bảng_2 ON bảng_1.khoá_chính = bảng_2.khoá_ngoại

Liệt kê tất cả các nhân viên và món hàng mà họ đặt (nếu có):

SELECT Employees.Name, Orders.Product FROM Employees LEFT JOIN Orders ON Employees.Employee_ID = Orders.Employee_ID

LEFT JOIN trả về tất cả các dòng của bảng thứ nhất (Employees), ngay cả khi các dòng đó không ứng với dòng nào ở bảng thứ hai (Orders). Nếu có một dòng nào ở bảng Employees không ứng với bất cứ dòng nào ở bảng Orders thì dòng đó cũng vẫn được tính.

kết quả trả về:

Name Product
Hansen, Ola Printer
Svendson, Tove
Svendson, Stephen Table
Svendson, Stephen Chair
Pettersen, Kari

Ví dụ: RIGHT JOIN

Cú pháp:

SELECT cột_1, cột_2, cột_3 FROM bảng_1 RIGHT JOIN bảng_2 ON bảng_1.khoá_chính = bảng_2.khoá_ngoại

Liệt kê tất cả các mặt hàng được đặt và tên người đặt hàng (nếu có):

SELECT Employees.Name, Orders.Product FROM Employees RIGHT JOIN Orders ON Employees.Employee_ID = Orders.Employee_ID

RIGHT JOIN trả về tất cả các dòng ở bảng thứ hai (Orders), ngay cả khi các dòng đó không ứng với dòng nào ở bảng thứ nhất (Employees). Nếu có một dòng nào ở bảng Orders không ứng với bất cứ dòng nào ở bảng Employees thì dòng đó cũng vẫn được tính.

kết quả trả về:

Name Product
Hansen, Ola Printer
Svendson, Stephen Table
Svendson, Stephen Chair

Ví dụ: Ai đã đặt hàng máy in:

SELECT Employees.Name FROM Employees INNER JOIN Orders ON Employees.Employee_ID = Orders.Employee_ID WHERE Orders.Product = 'Printer'

kết quả trả về:

Name
Hansen, Ola

Nguồn: blog.thegioiwebsite.net

Leave a comment

Lessons X : SQL – Các hàm tính toán

SQL – Các hàm tính toán

Thứ ba, 09 Tháng 9 2008 11:06 Blog.thegioiwebsite.Net Hướng dẫn lập trình SQL – MySQL
Email In

SQL có sẵn khá nhiều hàm để thực hiện đếm và tính toán.
Cú pháp:

Cú pháp để gọi hàm trong câu lệnh SQL như sau:

SELECT function(tên_cột) FROM tên_bảng

Bảng dữ liệu chúng ta sẽ dùng trong các ví sụ tiếp theo:

Name Age
Hansen, Ola 34
Svendson, Tove 45
Pettersen, Kari 19

Hàm AVG(column)

Hàm AVG trả về giá trị trung bình tính theo cột được chỉ định của các dòng được chọn. Các giá trị NULL sẽ không được xét đến khi tính giá trị trung bình.

Ví dụ:

Câu lệnh sau sẽ tính số tuổi trung bình của những người có tuổi trên 20:

SELECT AVG(Age) FROM Persons WHERE Age > 20

kết quả trả về sẽ là:

39.5

Hàm MAX(column)

Hàm MAX trả về giá trị lớn nhất trong cột. Các giá trị NULL sẽ không được xét đến.

Ví dụ:

SELECT MAX(Age) FROM Persons

kết quả trả về:

45

Hàm MIN(column)

Hàm MAX trả về giá trị nhỏ nhất trong cột. Các giá trị NULL sẽ không được xét đến.

Ví dụ:

SELECT MIN(Age) FROM Persons

kết quả trả về:

19

Lưu ý: Hàm MINMAX cũng có thể áp dụng cho các cột có dữ liệu là chuỗi văn bản. Dữ liệu trong cột sẽ được so sánh theo thứ tự tăng dần của từ điển

Hàm SUM(column)

Hàm SUM trả về tổng giá trị của cột. Các giá trị NULL sẽ không được xét đến.

Ví dụ:

Tìm tổng số tuổi của tất cả những người có trong bảng:

SELECT SUM(Age) FROM Persons

kết quả trả về:

98

Ví dụ:

Tìm tổng số tuổi của tất cả những người có tuổi lớn hơn 20:

SELECT SUM(Age) FROM Persons WHERE Age > 20

kết quả trả về:

79

Nguồn: Blog.thegioiwebsite.Net

Leave a comment

Lessons IX : Từ khoá DISTINCT

Từ khoá DISTINCT

Thứ ba, 09 Tháng 9 2008 11:04 Blog.thegioiwebsite.Net Hướng dẫn lập trình SQL – MySQL
Email In

Từ khoá DISTINCT được dùng để lọc ra các giá trị khác nhau.

 

Từ khoá DISTINCT

Câu lệnh SELECT sẽ trả về thông tin về các cột trong bảng. Nhưng nếu chúng ta không muốn lấy về các giá trị trùng nhau thì sau?

Với SQL, ta chỉ cần thêm từ khoá DISTINCT vào câu lệnh SELECT theo cú pháp sau:

SELECT DISTINCT tên_cột FROM tên_bảng

Ví dụ: Tìm tất cả các công ty trong bảng đặt hàng

Bảng đặt hàng của ta như sau:

Company OrderNumber
Sega 3412
W3Schools 2312
Trio 4678
W3Schools 6798

Câu lệnh SQL sau:

SELECT Company FROM Orders

Sẽ trả về kết quả:

Company
Sega
W3Schools
Trio
W3Schools

Tên công ty W3Schools xuất hiện hai lần trong kết quả, đôi khi đây là điều chúng ta không muốn.

Ví dụ: Tìm tất cả các công ty khác nhau trong bảng đặt hàng

Câu lệnh SQL sau:

SELECT DISTINCT Company FROM Orders

Sẽ trả về kết quả:

 

Company
Sega
W3Schools
Trio

Tên công ty W3Schools bây giờ chỉ xuất hiện 1 lần, đôi khi đây là điều chúng ta mong muốn.

Nguồn: Blog.thegioiwebsite.Net

Leave a comment

Lessons VIII : Toán tử BETWEEN…AND

Toán tử BETWEEN…AND

Thứ ba, 09 Tháng 9 2008 11:01 blog.thegioiwebsite.net Hướng dẫn lập trình SQL – MySQL
Email In

Toán tử BETWEEN…AND lấy ra một miền dữ liệu nằm giữa hai giá trị. Hai giá trị này có thể là số, chuỗi văn bản hoặc ngày tháng.

 SELECT tên_cột FROM tên_bảng WHERE tên_cột BETWEEN giá_trị_1 AND giá_trị_2

Bảng dữ liệu dùng trong ví dụ

LastName FirstName Address City
Hansen Ola Timoteivn 10 Sandnes
Nordmann Anna Neset 18 Sandnes
Pettersen Kari Storgt 20 Stavanger
Svendson Tove Borgvn 23 Sandnes

Ví dụ 1

Tìm tất cả những người có họ (sắp xếp theo ABC) nằm giữa Hansen (tính luôn Hansen) và Pettersen (không tính Pettersen):

SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'

Kết quả trả về:

 

LastName FirstName Address City
Hansen Ola Timoteivn 10 Sandnes
Nordmann Anna Neset 18 Sandnes

Lưu ý quan trọng: Toán tử BETWEEN...END sẽ trả về những kết quả khác nhau trên các hệ CSDL khác nhau. Với một số hệ CSDL, toán tử BETWEEN...END sẽ trả về các dòng mà có giá trị thực sự “nằm giữa” hai khoảng giá trị (tức là bỏ qua không tính đến các giá trị trùng với giá trị của hai đầu mút). Một số hệ CSDL thì sẽ tính luôn các giá trị trùng với hai đầu mút. Trong khi đó một số hệ CSDL khác lại chỉ tính các giá trị trùng với đầu mút thứ nhất mà không tính đầu mút thứ hai (như ở ví dụ phía trên). Do vậy, bạn phải kiểm tra lại hệ CSDL mà bạn đang dùng khi sử dụng toán tử BETWEEN...AND.

Ví dụ 2

Để tìm những người có họ (sắp xếp theo ABC) nằm ngoài khoảng hai giá trị ở ví dụ 1, ta dùng thêm toán tử NOT:

SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Hansen' AND 'Pettersen'

Kết quả trả về:

 

LastName FirstName Address City
Pettersen Kari Storgt 20 Stavanger
Svendson Tove Borgvn 23 Sandnes

Theo http://blog.thegioiwebsite.net/

Leave a comment

Lessons VII : SQL Server 2005 – Hack dữ liệu đã mã hoá bởi mật khẩu

SQL Server 2005 – Hack dữ liệu đã mã hoá bởi mật khẩu

Thứ tư, 20 Tháng 8 2008 02:53 Databasejournal Hướng dẫn lập trình SQL – MySQL

Trong phần 1 của loạt bài này, chúng tôi đã giới thiệu phương pháp mã hoá và giải mã bằng mật khẩu. Phần 2 này sẽ đi vào cách hack lại dữ liệu đó.


Như bạn đã biết, mã hoá bằng mật khẩu là một phương pháp mã hoá dữ liệu cơ bản chỉ sử dụng đến mật khẩu và có thể giải mã với cùng mật khẩu đó. Giờ hãy giả dụ chúng ta quên mất mật khẩu đã đặt và cần phải khôi phục lại dữ liệu như ban đầu.

Bước 1

Mã hoá dữ liệu theo phương pháp mã hoá bằng mật khẩu

select EncryptedData = EncryptByPassPhrase('MAK', '123456789' )

Kết quả

EncryptedData
--------------------------------------------------------------------------
0x01000000F75D553409C74570F6DDBCADA53FD489DDD52D9277010050565ADF30F244F8CC

Bước 2

Tạo thủ tục sử dụng để khôi phục lại dữ liệu đã má hoá. Thủ tục này sẽ sử dụng hàm DecryptByPassPhrase để giải mã dữ liệu và hiển thị lên mật khẩu.

USE [Master]
GO

/****** Object:  StoredProcedure [dbo].[hack_encryption] Script Date: 12/18/2007 18:18:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[hack_encryption]') 
                                      AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[hack_encryption]
GO
set nocount on
SET CONCAT_NULL_YIELDS_NULL OFF
go
USE [Master]
GO

/****** Object:  StoredProcedure [dbo].[hack_encryption] Script Date: 12/18/2007 18:18:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[hack_encryption] @encryptedtext varbinary(max)
as
declare @password varchar(6)
declare @i int
declare @j int
declare @k int
declare @l int
declare @m int
declare @n int

set @i=-1
set @j=-1
set @k=-1
set @l=-1
set @m=-1
set @n=-1
set @password =''

while @i<255
begin
   while @j<255
   begin
       while @k<255
       begin
                 while @l<255
                 begin
                   while @m<255
                   begin
                       while @n<=255
                       begin
                       set @password=isnull(char(@i),'') 
                               + isnull(char(@j),'')
                               +isnull(char(@k),'')+ isnull(char(@l),'')
                               +isnull(char(@m),'') + isnull(char(@n),'')
                       if convert(varchar(100), DecryptByPassPhrase(ltrim(rtrim(@password)),
                                                                            @encryptedtext)) is not null
                       begin
                       print 'This is the Encrypted text:' +@password
                       set @i=256;set @j=256;set @k=256;set @l=256;set @m=256;set @n=256;
                       print 'The actual data is :' +convert(varchar(100),
                                                    DecryptByPassPhrase(ltrim(rtrim(@password)), @encryptedtext))
                       end
                       --print 'A'+ltrim(rtrim(@password))+'B'
                       --print convert(varchar(100), DecryptByPassPhrase(ltrim(rtrim(@password)),@encryptedtext))
                       set @n=@n+1
                       end
                   set @n=0
                   set @m=@m+1
                   end
          set @m=0
          set @l=@l+1
          end
       set @l=0
       set @k=@k+1
       end
   set @k=0
   set @j=@j+1
   end
set @j=0
set @i=@i+1
end

GO

Bước 3

Giả sử rằng bạn đã quên mật khẩu dùng để mã hoá dữ liệu thành “0x01000000F75D553409C74570F6DDBCADA53FD489DDD52D9277010050565ADF30F244F8CC”. Chúng ta có thể truy tìm lại được mật khẩu và dữ liệu đã bị mã hoá bằng thủ tục sau

use master
go
select getdate() as StartingTime
go
declare @myencryptedtext varbinary(max)
set @myencryptedtext=0x01000000F75D553409C74570F6DDBCADA53FD489DDD52D9277010050565ADF30F244F8CC
print @myencryptedtext
exec hack_encryption @encryptedtext=@myencryptedtext
go
select getdate() as EndingTime
go

Kết quả

StartingTime
-----------------------
2007-12-18 18:24:10.843

0x01000000F75D553409C74570F6DDBCADA53FD489DDD52D9277010050565ADF30F244F8CC
This is the Encrypted text: MAK
The actual data is :123456789

EndingTime
-----------------------
2007-12-18 18:26:36.080

Hình 1

Như bạn thấy trong kết quả (hình 1), nó chỉ cần đển 2 phút để tìm lại được dữ liệu và mật khẩu. Về cơ bản, thủ tục này lặp lại tất cả khả năng hợp lý có thể xảy ra của các ký tự ASCII có độ dài trên 6 ký tự để tìm ra mật khẩu và sử dụng nó để giải mã dữ liệu.

Tạo ra một thủ tục sẽ không giúp gì nhiều khi dữ liệu đã được mã hoá nằm trong một bảng. Vì vậy chúng ta phải thay đổi thủ thục này thành một hàm vô hướng như hướng dẫn dưới đây

Bước 1

Tạo thủ tục như sau

USE [master]
GO

/****** Object:  UserDefinedFunction [dbo].[hack_encryption_password] Script Date: 12/18/2007 18:36:29 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[hack_encryption_password]')
                                      AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[hack_encryption_password]
GO
use [Master]
go

CREATE function [dbo].[hack_encryption_password] (@encryptedtext varbinary(max))
returns varchar(6)
with execute as caller
as
begin
declare @password varchar(6)
declare @i int
declare @j int
declare @k int
declare @l int
declare @m int
declare @n int

set @i=-1
set @j=-1
set @k=-1
set @l=-1
set @m=-1
set @n=-1
set @password =''

while @i<255
begin
    while @j<255
    begin
        while @k<255
        begin
            while @l<255
            begin
                while @m<255
                begin
                    while @n<=255
                    begin
                    set @password=isnull(char(@i),'') + isnull(char(@j),'')
                                  +isnull(char(@k),'')+ isnull(char(@l),'')
                                  +isnull(char(@m),'') + isnull(char(@n),'')
                    if convert(varchar(100), DecryptByPassPhrase(ltrim(rtrim(@password)),
                                                                         @encryptedtext)) is not null
                    begin
                    --print 'This is the Encrypted text:' +@password
                    set @i=256;set @j=256;set @k=256;set @l=256;set @m=256;set @n=256;
                    --print 'The actual data is :' +convert(varchar(100),
                                                   DecryptByPassPhrase(ltrim(rtrim(@password)),@encryptedtext))
                    end
                    --print 'A'+ltrim(rtrim(@password))+'B'
                    --print convert(varchar(100),DecryptByPassPhrase(ltrim(rtrim(@password)),@encryptedtext))
                    set @n=@n+1
                    end
                set @n=0
                set @m=@m+1
                end
            set @m=0
            set @l=@l+1
            end
        set @l=0
        set @k=@k+1
        end
    set @k=0
    set @j=@j+1
    end
set @j=0
set @i=@i+1
end

return @password
END

Bước 2

Tạo một bảng với dữ liệu được mã hoá

USE [tempdb]
GO
/****** Object: Table [dbo].[MyTable] Script Date: 12/18/2007 18:44:40 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO
create table MyTable(id int, encrypteddata varbinary(max))
go
insert into MyTable select 1, EncryptByPassPhrase('Do', '1112228333')
insert into MyTable select 2, EncryptByPassPhrase('Re', '1212223833')
insert into MyTable select 3, EncryptByPassPhrase('Me', '1132223393')
insert into MyTable select 4, EncryptByPassPhrase('Fa', '1114223383')
insert into MyTable select 5, EncryptByPassPhrase('So', '1112523333')
insert into MyTable select 6, EncryptByPassPhrase('La', '1112263373')
insert into MyTable select 7, EncryptByPassPhrase('Si', '1112227338')
go

Bước 3

Truy vấn dữ liệu sử dụng câu lệnh SQL sau

Select * from MyTable

Bạn sẽ thấy dữ liệu hiển thị như sau (hình 2)

1          0x01000000D8ED1498BEA4023D541C6EA9766A6B7B0585FAE91B942C88C23677550C6FD7FA
2          0x01000000F0725A52501A41D125F049011BE87C5C4A42263E7538B837B8278ADEE5FC2678
3          0x01000000C8804D8516B944B0AE35C71F79130DA415CED5CCF58E522692AC749115EEF0D9
4          0x010000007A91A24638C0E0354336AE5682805312CCB0B1E6BBACB6D9E65DC5D9DA73906E
5          0x010000008FB6BDD91C3D1A8C94FAF647DE1F931CEE5104045BD03DE4E809565E74604DF3
6          0x01000000C3A41428A21EDE8D8579AF9C42132678448A9113A31A869276A7631A58A32BE3
7          0x01000000BD829E12D3EAAF96BB66930301BA1D9CD3748946F354301922A03AE49047FE00

Hình 2

Bước 4

Sử dụng hàm hack_encryption_password để khôi phục tất cả các mật khẩu từ dưc liệu đã được mã hoá trong bảng MyTable. Thực thi câu lệnh SQL sau

select ID ,master.[dbo].[hack_encryption_password] (encrypteddata) as Password from MyTable

Bạn sẽ thấy kết quả như sau (Hình 3)

1          Do
2          Re
3          Me
4          Fa
5          So
6          La
7          Si 

Hình 3

Hàm trên có thê được chỉnh sửa để trả về cả dữ liệu đã được mã hoá, thực hiện như sau

Bước 1

Tạo hàm sau

USE [master]
GO

/****** Object:  UserDefinedFunction [dbo].[hack_encryption_password]    Script Date: 12/18/2007 18:36:29 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[hack_encryption_data]')
                                      AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[hack_encryption_data]
GO
use [Master]
go

CREATE function [dbo].[hack_encryption_data] (@encryptedtext varbinary(max))
returns varchar(8000)
with execute as caller
as
begin
declare @data varchar(8000)
declare @password varchar(6)
declare @i int
declare @j int
declare @k int
declare @l int
declare @m int
declare @n int

set @i=-1
set @j=-1
set @k=-1
set @l=-1
set @m=-1
set @n=-1
set @password =''

while @i<255
begin
    while @j<255
    begin
        while @k<255
        begin
            while @l<255
            begin
                while @m<255
                begin
                    while @n<=255
                    begin
                    set @password=isnull(char(@i),'') + isnull(char(@j),'')+isnull(char(@k),'')
                               + isnull(char(@l),'')+isnull(char(@m),'') + isnull(char(@n),'')
                    if convert(varchar(100),DecryptByPassPhrase(ltrim(rtrim(@password)),
                                                                        @encryptedtext)) is not null
                    begin
                    --print 'This is the Encrypted text:' +@password
                    set @i=256;set @j=256;set @k=256;set @l=256;set @m=256;set @n=256;
                    set @data = convert(varchar(100), DecryptByPassPhrase(ltrim(rtrim(@password)),@encryptedtext))
                    end
                    --print 'A'+ltrim(rtrim(@password))+'B'
                    --print convert(varchar(100), DecryptByPassPhrase(ltrim(rtrim(@password)),@encryptedtext))
                    set @n=@n+1
                    end
                set @n=0
                set @m=@m+1
                end
            set @m=0
            set @l=@l+1
            end
        set @l=0
        set @k=@k+1
        end
    set @k=0
    set @j=@j+1
    end
set @j=0
set @i=@i+1
end

return @data
END

Bước 2

Giải mã dữ liệu sử dụng hàm đã tạo

select ID ,master.[dbo].[hack_encryption_data] (encrypteddata) as Password from MyTable

Kết quả như hình 4

Hình 4

Lưu ý:

  • Thủ thục và các hàm chỉ có thể hack đối với mật khẩu dài 6 ký tự.
  • Thủ tục và hàm này có thể chiếm rất nhiều CPU để lấy lại dữ liệu và tìm lại mật khẩu

Theo Databasejournal
Nguồn: QuanTriMang.com

Leave a comment

Lessons VI : Mã hoá trong SQL Server 2005

Mã hoá trong SQL Server 2005

Thứ tư, 20 Tháng 8 2008 02:34 Databasejournal Hướng dẫn lập trình SQL – MySQL
Email In

Mã hoá là một phương pháp quan trọng nhằm bảo mật dữ liệu. Những dữ liệu nhạy cảm như số CMT, số thẻ tín dụng, mật khẩu… cần phải được bảo vệ trước vô vàn mối nguy hiểm tấn công hiện nay. Trong SQL Server 2000 bạn có thể tự tạo các hàm của riêng mình hoặc sử dụng các DLL ngoài để mã hoá dữ liệu. Trong SQL Server 2005, các hàm và phương thức này được mặc định cho phép sẵn.

SQL Server 2005 cung cấp các kỹ thuật sau để mã hoá dữ liệu

  • Mã hoá bằng mật khẩu
  • Mã hoá khoá đối xứng
  • Mã hoá khoá không đối xứng
  • Mã hoá chứng nhận

Trong phần đầu của loạt bài này, chúng tôi sẽ giải thích cách sử dụng kỹ thuật mã hoá bằng mật khẩu và phương pháp giải mã nó.

SQL Server 2005 cung cấp 2 hàm cho việc mã hoá: một cho việc mã hoá và một cho việc giải mã.

“Mã hoá bằng mật khẩu” là phương pháp mã hoá dữ liệu cơ bản thông qua mật khẩu. Dữ liệu có thể được giải mã nếu nhập đúng mật khẩu đã sử dụng khi mã hoá. Chúng ta sẽ thử một ví dụ về việc mã hoá và giải mã dữ liệu bằng kỹ thuật mã hoá thông qua mật khẩu.

select EncryptedData = EncryptByPassPhrase('MAK', '123456789' )

Kết quả

EncryptedData
0x0100000000214F5A73054F3AB954DD23571154019F3EFC031ABFCCD258FD22ED69A48002

Giờ chúng ta sẽ thực thi 3 lần hàm Encryptbypassphrase trên theo ví dụ sau

declare @count int declare @SocialSecurityNumber varchar(500) declare @password varchar(12) set @count =1 while @count<=3 begin set @SocialSecurityNumber = '123456789' set @Password = 'MAK' select EncryptedData = EncryptByPassPhrase(@password, @SocialSecurityNumber ) set @count=@count+1 end

Kết quả

EncryptedData 0x01000000CBB7EE45B5C1460D6996B149CE16B76C7F7CD598DC56364D106B05D47B930093 (1 row(s) affected) EncryptedData 0x010000005E884D30C8FF7E4723D4E70A03B0B07F877667BAF1DA9BE1E116434842D11B99 (1 row(s) affected) EncryptedData 0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD

Lưu ý:
“123456789” ở đây có thể là số thẻ tín dụng và “MAK” là mật khẩu

Kết quả của Encryptbypassphrase sau mỗi lần thực thi hàm là khác nhau. Tuy nhiên, khi bạn giải mã dữ liệu thì nó vẫn ra kết quả như ban đầu trước khi mã hoá.

Giờ chúng ta sẽ thử giải mã dữ liệu đã được mã hoá ở trên với hàm DecryptByPassPhrase

select convert(varchar(100),DecryptByPassPhrase('MAK', 0x01000000CBB7EE45B5C1460D6996B149CE16B76C7F7CD598DC56364D106B05D47B930093)) select convert(varchar(100),DecryptByPassPhrase('MAK', 0x010000005E884D30C8FF7E4723D4E70A03B0B07F877667BAF1DA9BE1E116434842D11B99)) select convert(varchar(100),DecryptByPassPhrase('MAK', 0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD)) 

Kết quả

123456789 (1 row(s) affected) 123456789 (1 row(s) affected) 123456789 (1 row(s) affected)

Thử giải mã dữ liệu đã được mã hoá với một mật khẩu khác. Thực thi theo câu lệnh sau

select convert(varchar(100),DecryptByPassPhrase('test', 0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD)) 


Kết quả

NULL (1 row(s) affected) 

Kết quả cho bạn thấy SQL Server trả lại giá trị NULL nếu mật khẩu sai.

Giờ chúng ta sẽ thử tạo một bảng chứa số thẻ tín dụng và số CMT, sau đó sẽ mã hoá dữ liệu này thông qua phương pháp mã hoá mật khẩu.

USE [master]
GO
/****** Object: Database [admin] Script Date: 11/25/2007 10:50:47 ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N’Customer DB’)
DROP DATABASE [Customer DB]
go

create database [Customer DB]
go
use [Customer DB]
go

create table [Customer data]
([customer id] int,
[Credit Card Number] bigint,
[Social Security Number] bigint)
go

insert into [Customer data] values (1, 1234567812345678, 123451234)
insert into [Customer data] values (2, 1234567812345378, 323451234)
insert into [Customer data] values (3, 1234567812335678, 133451234)
insert into [Customer data] values (4, 1234567813345678, 123351234)
insert into [Customer data] values (5, 1234563812345678, 123431234)
go

Tạo hai cột để lưu dữ liệu đã được mã hoá

use [Customer DB]
go
alter table [Customer Data] add
[Encrypted Credit Card Number] varbinary(MAX)
go
alter table [Customer Data] add
[Encrypted Social Security Number] varbinary(MAX)
go

Cập nhật dữ liệu đã được mã hoá vào hai cột vừa tạo
use [Customer DB]
go
update [Customer Data] set [Encrypted Credit Card Number] =
EncryptByPassPhrase(‘Credit Card’, convert(varchar(100),[Credit Card Number]) )
go
update [Customer Data] set [Encrypted Social Security Number] =
EncryptByPassPhrase(‘Social Security’, convert(varchar(100),[Social Security Number]) )
Go

Truy vẫn bảng bằng các lệnh sau (hình 1)

use [Customer DB] go select * from [customer data] go 

Kết quả


Hình 1

Xoá bỏ cột chứa dữ liệu chưa được mã hoá

use [Customer DB] go alter table [Customer Data] drop column [Credit Card Number] go alter table [Customer Data] drop column [Social Security Number] go 

Truy vấn bảng theo các lệnh sau (hình 2)

use [Customer DB] go select * from [customer data] go 

Kết quả


Hình 2

Giải mã dữ liệu trên bảng thông qua hàm Decryptbypassphrase như sau (hình 3)

use [Customer DB]
go
select
[customer id],
convert(bigint,convert(varchar(100),decryptbypassphrase('Credit Card',[Encrypted Credit Card Number]) )) as
[Credit Card Number],
convert(bigint,convert(varchar(100),decryptbypassphrase('Social Security',[Encrypted Social Security Number] ) )) as
[Social Security Number] from [customer data]
Go 

Kết quả

customer id,Credit Card Number,Social Security Number 1, 1234567812345678, 123451234 2, 1234567812345378, 323451234 3, 1234567812335678, 133451234 4, 1234567813345678, 123351234 5, 1234563812345678, 123431234 


Hình 3

Kết luận

Mã hoá dữ liệu thực sự rất quan trọng. Thông qua bài này chúng tôi đã giới thiệu đến các bạn một trong 4 kỹ thuật mã hoá sẵn có trong SQL Server 2005 – kỹ thuật mã hoá bằng mật khẩu – và phương pháp giải mã nó. Trong bài sau, chúng ta sẽ bàn luận về phương pháp hack/khôi phục dữ liệu đã được mã hoá bằng mật khẩu này.

Nguồn: QuanTriMang.com
Theo Databasejournal

Leave a comment

Lessons V : Cấu Trúc Của SQL Server

Cấu Trúc Của SQL Server

Thứ hai, 18 Tháng 8 2008 15:18 vovisoft.com Hướng dẫn lập trình SQL – MySQL
Email In

Một trong những đặc điểm của SQL Server 2000 là Multiple-Instance nên khi nói đến một (SQL) Server nào đó là ta nói đến một Instance của SQL Server 2000, thông thường đó là Default Instance. Một Instance của SQL Server 2000 có 4 system databases và một hay nhiều user database. Các system databases bao gồm:

  • Master : Chứa tất cả những thông tin cấp hệ thống (system-level information) bao gồm thông tin về các database khác trong hệ thống như vị trí của các data files, các login account và các thiết đặt cấu hình hệ thống của SQL Server (system configuration settings).
  • Tempdb : Chứa tất cả những table hay stored procedure được tạm thời tạo ra trong quá trình làm việc bởi user hay do bản thân SQL Server engine. Các table hay stored procedure này sẽ biến mất khi khởi động lại SQL Server hay khi ta disconnect.
  • Model : Database này đóng vai trò như một bảng kẻm (template) cho các database khác. Nghĩa là khi một user database được tạo ra thì SQL Server sẽ copy toàn bộ các system objects (tables, stored procedures…) từ Model database sang database mới vừa tạo.
  • Msdb : Database này được SQL Server Agent sử dụng để hoạch định các báo động và các công việc cần làm (schedule alerts and jobs).

Cấu Trúc Vật Lý Của Một SQL Server Database

Mỗi một database trong SQL Server đều chứa ít nhất một data file chính (primary), có thể có thêm một hay nhiều data file phụ (Secondary) và một transaction log file.

  • Primary data file (thường có phần mở rộng .mdf) : đây là file chính chứa data và những system tables.
  • Secondary data file (thường có phần mở rộng .ndf) : đây là file phụ thường chỉ sử dụng khi database được phân chia để chứa trên nhiều dĩa.
  • Transaction log file (thường có phần mở rộng .ldf) : đây là file ghi lại tất cả những thay đổi diễn ra trong một database và chứa đầy đủ thông tin để có thể roll back hay roll forward khi cần.

Data trong SQL Server được chứa thành từng Page 8KB và 8 page liên tục tạo thành một Extent như hình vẽ dưới đây:

Trước khi SQL Server muốn lưu data vào một table nó cần phải dành riêng một khoảng trống trong data file cho table đó. Những khoảng trống đó chính là các extents. Có 2 loại Extents: Mixed Extents (loại hỗn hợp) dùng để chứa data của nhiều tables trong cùng một Extent và Uniform Extent (loại thuần nhất) dùng để chứa data của một table. Ðầu tiên SQL Server dành các Page trong Mixed Extent để chứa data cho một table sau đó khi data tăng trưởng thì SQL dành hẳn một Uniform Extent cho table đó.

Nguyên Tắc Hoạt Ðộng Của Transaction Log Trong SQL Server

Transaction log file trong SQL Server dùng để ghi lại các thay đổi xảy ra trong database. Quá trình này diễn ra như sau: đầu tiên khi có một sự thay đổi data như Insert, Update, Delete được yêu cầu từ các ứng dụng, SQL Server sẽ tải (load) data page tương ứng lên memory (vùng bộ nhớ này gọi là data cache), sau đó data trong data cache được thay đổi(những trang bị thay đổi còn gọi là dirty-page). Tiếp theo mọi sự thay đổi đều được ghi vào transaction log file cho nên người ta gọi là write-ahead log. Cuối cùng thì một quá trình gọi là Check Point Process sẽ kiểm tra và viết tất cả những transaction đã được commited (hoàn tất) vào dĩa cứng (flushing the page).

xem hinh => click here

Ngoài Check Point Process những dirty-page còn được đưa vào dĩa bởi một Lazy writer. Ðây là một anh chàng làm việc âm thầm chỉ thức giấc và quét qua phần data cache theo một chu kỳ nhất định sau đó lại ngủ yên chờ lần quét tới.

Xin giải thích thêm một chút về khái niệm transaction trong database. Một transaction hay một giao dịch là một loạt các hoạt động xảy ra được xem như một công việc đơn (unit of work) nghĩa là hoặc thành công toàn bộ hoặc không làm gì cả (all or nothing). Sau đây là một ví dụ cổ điển về transaction:

 

Chúng ta muốn chuyển một số tiền $500 từ account A sang
account B như vậy công việc này cần làm các bước sau:
  1. Trừ $500 từ account A
  2. Cộng $500 vào account B

 

Tuy nhiên việc chuyển tiền trên phải được thực hiện dưới dạng một transaction nghĩa là giao dịch chỉ được
xem là hoàn tất (commited) khi cả hai bước trên đều thực hiện thành công. Nếu vì một lý do nào đó ta chỉ
có thể thực hiện được bước 1 (chẳng hạn như vừa xong bước 1 thì điện cúp hay máy bị treo) thì xem như giao
dịch không hoàn tất và cần phải được phục hồi lại trạng thái ban đầu (roll back).

 

Thế thì Check Point Process hoạt động như thế nào để có thể đảm bảo một transaction được thực thi mà không làm “dơ” database.

Trong hình vẽ trên, một transaction được biểu diễn bằng một mũi tên. Trục nằm ngang là trục thời gian. Giả sử một Check Point được đánh dấu vào thời điểm giữa transaction 2 và 3 như hình vẽ và sau đó sự cố xãy ra trước khi gặp một Check point kế tiếp. Như vậy khi SQL Server được restart nó sẽ dựa trên những gì ghi trong transaction log file để phục hồi data (xem hình vẽ).

Ðiều đó có nghĩa là SQL Server sẽ không cần làm gì cả đối với transaction 1 vì tại thời điểm Check point data đã được lưu vào dĩa rồi. Trong khi đó transaction 2 và 4 sẽ được roll forward vì tuy đã được commited nhưng do sự cố xảy ra trước thời điểm check point kế tiếp nên data chưa kịp lưu vào dĩa. Tức là dựa trên những thông tin được ghi trên log file SQL Server hoàn toàn có đầy đủ cơ sở để viết vào dĩa cứng. Còn transaction 3 và 5 thì chưa được commited (do bị down bất ngờ) cho nên SQL Server sẽ roll back hai transaction này dựa trên những gì được ghi trên log file.

Cấu Trúc Logic Của Một SQL Server Database

Hầu như mọi thứ trong SQL Server được tổ chức thành những objects ví dụ như tables, views, stored procedures, indexes, constraints…. Những system objects trong SQL Server thường có bắt đầu bằng chữ sys hay sp. Các objects trên sẽ được nghiên cứu lần lượt trong các bài sau do đó trong phần này chúng ta chỉ bàn sơ qua một số system object thông dụng trong  SQL Server database mà thôi.

Một số Sytem objects thường dùng:

 

System Stored Procedure
Ứng dụng
Sp_help [‘object’]
Cung cấp thông tin về một database object (table, view…) hay một data type.
Sp_helpdb [‘database’]
Cung cấp thông tin về một database cụ thể nào đó.
Sp_monitor
Cho biết độ bận rộn của SQL Server
Sp_spaceused [‘object’, ‘updateusage’ ]
Cung cấp thông tin về các khoảng trống đã được sử dụng cho một object nào đó
Sp_who [‘login’]
Cho biết thông tin về một SQL Server user

Ví dụ:

sp_helpdb ‘Northwind’ sẽ cho kết quả có dạng như bảng dưới đây

name             db_size     owner     dbid     created         status …..
——————————————————————————————————————————– ——-
Northwind     3.94 MB     sa           6         Aug 6 2000     Status=ONLINE, Updateability=READ_WRITE, …..

stored procedure  sp_spaceused như ví dụ sau

USE Northwind
Go
sp_spaceused ‘Customers’

sẽ cho biết thông tin về table Customer:

name             rows     reserved       data        index_size     unused
————————————- ——————————————
Customers     91         104 KB         24 KB      80 KB             0 KB
Tạo Một User Database

Chúng ta có thể tạo một database dễ dàng dùng SQL Server Enterprise bằng cách right-click lên trên “database” và chọn “New Database” như hình vẽ sau:

Sau đó chúng ta chỉ việc đánh tên của database và click OK.

Ngoài ra đôi khi chúng ta cũng dùng SQL script để tạo một database. Khi đó ta phải chỉ rõ vị trí của primary data file và transaction log file.

Ví dụ:

USE master
GO
CREATE DATABASE Products
ON
(  NAME = prods_dat,
   FILENAME = ‘c:\program files\microsoft SQL server\mssql\data\prods.mdf’,
   SIZE = 4,
   MAXSIZE = 10,
   FILEGROWTH = 1
)
GO

Trong ví dụ trên ta tạo một database tên là Products với logical file name là prods_dat và physical file name là prods.mdf, kích thước ban đầu là 4 MB và data file sẽ tự động tăng lên mỗi lần 1 MB cho tới tối đa là 10 MB. Nếu ta không chỉ định một transaction log file thì SQL sẽ tự động tạo ra 1 log file với kích thước ban đầu là 1 MB.

Lưu Ý:

Khi tạo ra một database chúng ta cũng phải lưu ý một số điểm sau: Ðối với các hệ thống nhỏ mà ở đó vấn đề tốc độ của server không thuộc loại nhạy cảm thì chúng ta thường chọn các giá trị mặc định (default) cho Initial size, Automatically growth file. Nhưng trên một số production server của các hệ thống lớn kích thước của database phải được người DBA ước lượng trước tùy theo tầm cỡ của business, và thông thường người ta không chọn Autogrowth(tự động tăng trưởng) và Autoshrink(tự động nén). Câu hỏi được đặt ra ở đây là vì sao ta không để SQL Server chọn một giá trị khởi đầu cho datafile và sau đó khi cần thì nó sẽ tự động nới rộng ra mà lại phải ước lượng trước? Nguyên nhân là nếu chọn Autogrowth (hay Autoshrink) thì chúng ta có thể sẽ gặp 2 vấn đề sau:

  • Performance hit: Ảnh hưởng đáng kể đến khả năng làm việc của SQL Server. Do nó phải thường xuyên kiểm tra xem có đủ khoảng trống cần thiết hay không và nếu không đủ nó sẽ phải mở rộng bằng cách dành thêm khoảng trống từ dĩa cứng và chính quá trình này sẽ làm chậm đi hoạt động của SQL Server.
  • Disk fragmentation : Việc mở rộng trên cũng sẽ làm cho data không được liên tục mà chứa ở nhiều nơi khác nhau trong dĩa cứng điều này cũng gây ảnh hưởng lên tốc độ làm việc của SQL Server.

Trong các hệ thống lớn người ta có thể dự đoán trước kích thước của database bằng cách tính toán kích thước của các tables, đây cũng chỉ là kích thước ước đoán mà thôi (xin xem “Estimating the size of a database” trong SQL Books Online để biết thêm về cách tính) và sau đó thường xuyên dùng một số câu lệnh SQL (thường dùng các câu lệnh bắt đầu bằng DBCC .Phần này sẽ được bàn qua trong các bài sau) kiểm tra xem có đủ khoảng trống hay không nếu không đủ ta có thể chọn một thời điểm mà SQL server ít bận rộn nhất (như ban đêm hay sau giờ làm việc) để nới rộng data file như thế sẽ không làm ảnh hưởng tới performance của Server.

Chú ý giả sử ta dành sẵn 2 GB cho datafile, khi dùng Window Explorer để xem ta sẽ thấy kích thước của file là 2 GB nhưng data thực tế có thể chỉ chiếm vài chục MB mà thôi.

Những Ðiểm Cần Lưu Ý Khi Thiết Kế Một Database

Trong phạm vi bài này chúng ta không thể nói sâu về lý thuyết thiết kế database mà chỉ đưa ra một vài lời khuyên mà bạn nên tuân theo khi thiết kế.

Trước hết bạn phải nắm vững về các loại data type. Ví dụ bạn phải biết rõ sự khác biệt giữa char(10), nchar(10) varchar(10), nvarchar(10). Loại dữ liệu Char là một loại string có kích thước cố định nghĩa là trong ví dụ trên nếu data đưa vào “This is a really long character string” (lớn hơn 10 ký tự) thì SQL Server sẽ tự động cắt phần đuôi và ta chỉ còn “This is a”. Tương tự nếu string đưa vào nhỏ hơn 10 thì SQL sẽ thêm khoảng trống vào phía sau cho đủ 10 ký tự. Ngược lại loại varchar sẽ không thêm các khoảng trống phía sau khi string đưa vào ít hơn 10. Còn loại data bắt đầu bằng chữ n chứa dữ liệu dạng unicode.

Một lưu ý khác là trong SQL Server ta có các loại Integer như : tinyint, smallint, int, bigint. Trong đó kích thước từng loại tương ứng là 1,2,4,8 bytes. Nghĩa là loại smallint tương đương với Integer và loại int tương đương với Long trong VB.

Khi thiết kế table nên:

  • Có ít nhất một cột thuộc loại ID dùng để xác định một record dễ dàng.
  • Chỉ chứa data của một entity (một thực thể)

Trong ví dụ sau thông tin về Sách và Nhà Xuất Bản được chứa trong cùng một table

Books

BookID Title Publisher PubState PubCity PubCountry
1 Inside SQL Server 2000 Microsoft Press CA Berkely USA
2 Windows 2000 Server New Riders MA Boston USA
3 Beginning Visual Basic 6.0 Wrox CA Berkely USA

Ta nên tách ra thành table Books và table Publisher như sau:

Books

BookID Title PublisherID
1 Inside SQL Server 2000 P1
2 Windows 2000 Server P2
3 Beginning Visual Basic 6.0 P3

Publishers

PublisherID Publisher PubState PubCity PubCountry
P1 Microsoft Press CA Berkely USA
P2 New Riders MA Boston USA
P3 Wrox CA Berkely USA
  • Tránh dùng cột có chứa NULL và nên luôn có giá trị Default cho các cột
  • Tránh lập lại một giá trị hay cột nào đó

Ví dụ một cuốn sách có thể được viết bởi hơn một tác giả và như thế ta có thể dùng một trong 2 cách sau để chứa data:

Books

BookID Title Authors
1 Inside SQL Server 2000 John Brown
2 Windows 2000 Server Matthew Bortniker, Rick Johnson
3 Beginning Visual Basic 6.0 Peter Wright, James Moon, John Brown

hay

Books

BookID Title Author1 Author2 Author3
1 Inside SQL Server 2000 John Brown Null Null
2 Windows 2000 Server Matthew Bortniker Rick Johnson Null
3 Beginning Visual Basic 6.0 Peter Wright James Moon John Brown

Tuy nhiên việc lập đi lập lại cột Author sẽ tạo nhiều vấn đề sau này. Chẳng hạn như nếu cuốn sách có nhiều hơn 3 tác giả thì chúng ta sẽ gặp phiền phức ngay….Trong ví dụ này ta nên chặt ra thành 3 table như sau:

Books

BookID Title
1 Inside SQL Server 2000
2 Windows 2000 Server
3 Beginning Visual Basic 6.0

Authors

AuthID First Name Last Name
A1 John Brown
A2 Matthew Bortniker
A3 Rick Johnson
A4 Peter Wright
A5 James Moon

AuthorBook

BookID AuthID
1 A1
2 A2
2 A3
3 A4
3 A5
3 A1

Ngoài ra một trong những điều quan trọng là phải biết rõ quan hệ (Relationship) giữa các table:

  • One-to-One Relationships : trong mối quan hệ này thì một hàng bên table A không thể liên kết với hơn 1 hàng bên table B và ngược lại.
  • One-to-Many Relationships : trong mối quan hệ này thì một hàng bên table A có thể liên kết với nhiều hàng bên table B.
  • Many-to-Many Relationships : trong mối quan hệ này thì một hàng bên table A có thể liên kết với nhiều hàng bên table B và một hàng bên table B cũng có thể liên kết với nhiều hàng bên table A. Như ta thấy trong ví dụ trên một cuốn sách có thể được viết bởi nhiều tác giả và một tác giả cũng có thể viết nhiều cuốn sách. Do đó mối quan hệ giữa Books và Authors là quan hệ Many to Many. Trong trường hợp này người ta thường dùng một table trung gian để giải quyết vấn đề (table AuthorBook).

Ðể có một database tương đối hoàn hảo nghĩa là thiết kế sao cho data chứa trong database không thừa không thiếu bạn cần biết thêm về các thủ thuật Normalization. Tuy nhiên trong phạm vi khóa học này chúng tôi không muốn bàn sâu hơn về đề tài này, bạn có thể xem thêm trong các sách dạy lý thuyết cơ sở dữ liệu.

Tóm lại trong bài này chúng ta đã tìm hiểu về cấu trúc của một SQL Server database và một số vấn đề cần biết khi thiết kế một database. Trong bài sau chúng ta sẽ bàn về Backup và Restore database như thế nào.

Nguồn: vovisoft.com

Leave a comment