· 7 years ago · Jan 13, 2019, 10:08 PM
1o login está certo
2
3DELIMITER $$
4 CREATE TABLE `tbl_users` (
5 `id` int(11) NOT NULL AUTO_INCREMENT,
6 `username` varchar(15) NOT NULL,
7 `firstlast_name` varchar(80) DEFAULT NULL,
8 `password` varchar(45) DEFAULT NULL,
9 PRIMARY KEY (`id`)
10)$$
11 DELIMITER ;
12
13 DELIMITER $$
14 DROP FUNCTION IF EXISTS `fun_valida_usuario`$$
15 CREATE FUNCTION `fun_valida_usuario`(p_username VARCHAR(15)
16 , p_password VARCHAR(45) ) RETURNS INT(1)
17 BEGIN
18 DECLARE _ret INT(1) DEFAULT 0;
19 SET _ret = IFNULL((SELECT DISTINCT 1
20 FROM tbl_users
21 WHERE `username` = p_username
22 AND `password` = MD5(p_password)),0);
23 RETURN _ret;
24 END$$
25 DELIMITER ;
26
27DELIMITER $$
28CREATE PROCEDURE `proc_iae_tbl_users`(
29p_opc varchar(1),
30p_id int,
31p_username varchar(15),
32p_firstlast_name varchar(80),
33p_password varchar(45))
34BEGIN
35
36IF ((p_opc = 'I') && (p_username != '') && (p_password != '')) THEN
37
38 INSERT INTO tbl_users (id, username, firstlast_name, `password`) VALUES (p_id, p_username, p_firstlast_name, MD5(p_password));
39
40ELSE
41
42IF ((p_opc = 'E') && (p_id > 0)) THEN
43
44 delete from tbl_users where id = p_id;
45
46ELSE
47
48IF ((p_opc = 'A')) THEN
49
50 UPDATE tbl_users set id = p_id, username = p_username, firstlast_name = p_firstlast_name, `password` = p_password WHERE id = p_id;
51
52ELSE
53SELECT 'Você não pode realizar as alterações' AS Msg;
54END IF;
55END IF;
56END IF;
57
58END$$
59DELIMITER ;
60
61unit Unit1;
62
63interface
64
65uses
66 Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
67 Dialogs, WideStrings, DBXMySql, DB, SqlExpr, FMTBcd, Provider, DBClient,
68 StdCtrls, Mask, DBCtrls;
69
70type
71 TForm1 = class(TForm)
72 SQLConnection1: TSQLConnection;
73 ClientDataSet1: TClientDataSet;
74 DataSetProvider1: TDataSetProvider;
75 SQLQuery1: TSQLQuery;
76 SQLQuery1id: TIntegerField;
77 SQLQuery1username: TStringField;
78 SQLQuery1firstlast_name: TStringField;
79 SQLQuery1password: TStringField;
80 DataSource1: TDataSource;
81 ClientDataSet1id: TIntegerField;
82 ClientDataSet1username: TStringField;
83 ClientDataSet1firstlast_name: TStringField;
84 ClientDataSet1password: TStringField;
85 InsertUser: TButton;
86 Edit1: TEdit;
87 Edit2: TEdit;
88 proc_iae_tbl_users: TSQLStoredProc;
89 proc_iae_tbl_usersMsg: TStringField;
90 Edit3: TEdit;
91 Edit4: TEdit;
92 Edit5: TEdit;
93 VerifyLogin: TButton;
94 Label1: TLabel;
95 Label2: TLabel;
96 SQLStoredProc1: TSQLStoredProc;
97 SQLStoredProc1Value: TIntegerField;
98 Label3: TLabel;
99 DBEdit1: TDBEdit;
100 DataSource2: TDataSource;
101 SQLQuery2: TSQLQuery;
102 SQLQuery2Valor: TIntegerField;
103 Label4: TLabel;
104 DBEdit2: TDBEdit;
105 DataSource3: TDataSource;
106 procedure Button1Click(Sender: TObject);
107 procedure Button2Click(Sender: TObject);
108 private
109 { Private declarations }
110 public
111 { Public declarations }
112 end;
113
114var
115 Form1: TForm1;
116
117implementation
118
119{$R *.dfm}
120
121procedure TForm1.InsertUserClick(Sender: TObject);
122begin
123 proc_iae_tbl_users.Close;
124 proc_iae_tbl_users.ParamByName('p_opc').Value:= 'I';
125 proc_iae_tbl_users.ParamByName('p_username').Value:= Edit1.Text;
126 proc_iae_tbl_users.ParamByName('p_firstlast_name').Value:= Edit2.Text;
127 proc_iae_tbl_users.ParamByName('p_password').Value:= Edit3.Text;
128 proc_iae_tbl_users.ExecProc;
129end;
130
131procedure TForm1.VerifyLoginClick(Sender: TObject);
132begin
133 // Aqui deve ficar o código para logar e verificar o login
134end;
135
136end.
137
138DM.sql_login.Close;
139 DM.sql_login.SQL.Clear;
140 DM.sql_login.SQL.Add('SELECT fun_valida_usuario("'+edt_username.Text+'","'+edt_password.Text+'") AS Ret');
141 DM.sql_login.Open;
142
143 if (DM.sql_login['Ret'] = 1 ) then
144 begin
145 DM.sql_login.Close;
146 Close;
147 end
148 else
149 begin
150 MessageBox(handle,'Usuário ou Senha estão incorretos','Aviso',mb_IconError + mb_OK);
151 end;
152
153StoredProc.Open;
154try
155 CredenciaisValidas := StoredProc.FieldByName('Result').AsInteger = 1;
156finally
157 StoredProc.Close;