Topic: please help, Can the project be connect with Microsoft sql server?

Can the project be connect with Microsoft sql server?

Re: please help, Can the project be connect with Microsoft sql server?

procedure frm_main_Button1_OnClick (Sender: TObject; var Cancel: boolean);
var
  Conn, rs : Variant;
  db_settings, a,b,c: String;

  i,j: int;
var NxTextColumn: TNxTextColumn;
begin
   {***  create database objects ****  }
   Conn :=createoleobject('ADODB.Connection') ; //
   rs := createoleobject('ADODB.recordset');

   {** open database connection ***  }
  db_settings:='Provider=SQLOLEDB;Server=localhost;database=DatabaseName;UID=UserName;PWD=password';    //connection to database
   Conn.Open(db_settings);    //connection to database

   { query to get data}
   rs.Open  ('Select name,dob,address from tablename', Conn); // sql query

    //create table information
    try frm_main.TableGrid1.Columns.Clear; except end;
    try frm_main.TableGrid1.Columns.Add(TNxTextColumn); except end;
    try frm_main.TableGrid1.Columns.Add(TNxTextColumn); except end;
    try frm_main.TableGrid1.Columns.Add(TNxTextColumn); except end;




    //header name
    frm_main.TableGrid1.Columns[0].Header.Caption := 'Name';
    frm_main.TableGrid1.Columns[1].Header.Caption := 'DateofBirth';
    frm_main.TableGrid1.Columns[2].Header.Caption := 'Address';



   //initialized i
   i:=0;

   //while loop to get data from db
   while not rs.Eof do
    begin

        Application.ProcessMessages;

         //retrieve data and stored them in variables
         a:= trim(rs.Fields['Name']);
         b:= trim(rs.Fields['dob']);
         c:=trim(rs.Fields['Address']);

        //generate the data in Table Grid
        frm_main.TableGrid1.AddRow(1);
        frm_main.TableGrid1.Cells[0,i]:=a;
        frm_main.TableGrid1.Cells[1,i]:= b;
        frm_main.TableGrid1.Cells[2,i]:=c;

        i:=i+1; //increment i
        rs.movenext ;   //next record
    end;
 
     //close connection
     rs.Close;
     Conn.Close;

end;

Re: please help, Can the project be connect with Microsoft sql server?

another option

var
    Conn, rs: Variant;
    NxTextColumn: TNxTextColumn;
//---------------------------------------------





procedure Form1_Button1_OnClick (Sender: TObject; var Cancel: boolean);
var
    i: Integer;
begin
   
    rs.Open  (' Select name,fathername,mobileno', Conn);

   
 


    try Form1.TableGrid1.Columns.Clear; except end;
    try Form1.TableGrid1.Columns.Add(TNxTextColumn); except end;
    try Form1.TableGrid1.Columns.Add(TNxTextColumn); except end;
    try Form1.TableGrid1.Columns.Add(TNxTextColumn); except end;
 
    Form1.TableGrid1.Columns[0].Header.Caption := 'name';
    Form1.TableGrid1.Columns[1].Header.Caption := 'fathername';
    Form1.TableGrid1.Columns[2].Header.Caption := 'mobileno';

    Form1.TableGrid1.Columns[0].Color := ClWhite;
    Form1.TableGrid1.Columns[1].Color := ClWhite;
    Form1.TableGrid1.Columns[2].Color := ClWhite;

    Form1.TableGrid1.Font.Color := ClBlack;
    Form1.TableGrid1.SelectedRow := -1;
    i := 0;
    while not rs.Eof do begin
        Form1.TableGrid1.AddRow(1);
        Form1.TableGrid1.Cells[0,i]:= rs.Fields['name'];
        Form1.TableGrid1.Cells[1,i]:= trim(rs.Fields['fathername']);
        Form1.TableGrid1.Cells[2,i]:= trim(rs.Fields['mobileno']);

        Inc(i); // i := i+1
        rs.movenext
    end;
    rs.close;
end;

procedure Form1_OnClose (Sender: TObject; Action: string);
begin
    Conn.close;
end;


//-------------------Connection to database-----------------------------////

begin

     Conn :=createoleobject('ADODB.Connection') ; //
     rs := createoleobject('ADODB.recordset');
     Conn.Open('Provider=SQLOLEDB;Server=localhost;database=yourdatabaename;UID=username;PWD=userpassword');
// you can access database using ipaddress .replace localhost with ipaddress
    Form1.Button1.Click;
end.

Re: please help, Can the project be connect with Microsoft sql server?

Is there a more secure way to connect to a SQL Server, everything is readable in the script file. Could it be possible to connect using ODBC connection directly so it is not hard coded?

5 (edited by brian.zaballa 2022-10-05 23:02:22)

Re: please help, Can the project be connect with Microsoft sql server?

tcoton wrote:

Is there a more secure way to connect to a SQL Server, everything is readable in the script file. Could it be possible to connect using ODBC connection directly so it is not hard coded?

Here's my way of adding security to it. I think direct ODBC connection is not possible in Pascal Script.

Post's attachments

Attachment icon encode_credentials.zip 547.71 kb, 159 downloads since 2022-10-06 

brian

6 (edited by CDB 2022-10-06 08:22:20)

Re: please help, Can the project be connect with Microsoft sql server?

Thinking about how to make the connection secure, by which I take you mean the connection string is obfuscated in some way, I came up with the following idea. Not tested, so have no idea if it would work, and is probably more complicated than needs be! smile

1. Create a SQLite database that has just one table and for security about 5 fields.
..... Field 1 contains an encrypted form of the connection path details.
..... Field 2 ........   contains the encryption key spread across one or more fields, the other fields  contain dummy information and or the password
.

2. Before opening the SQL Server connection as demonstrated above, read in the SQLite database contents and perform the deincryption.

3. Parse the results into the actual  db_settings:='Provider=SQLOLEDB;Server=localhost;database=DatabaseName;UID=UserName;PWD=password';
 
To make it more secure, a function could be written that actually changes the encryption key (some sort of randomise function) each time the program is run, This could be placed on the MainForms OnClose event where the new encrypted path and key can be written back to the database. I assume the SQL Server DB would be disconnected at this point as well.

I've probably over thought this far more than is necessary, but if it worked, this idea could also be used for other settings in the ini file such as column widths in tablegrids etc.

The other option would be to have the encryption or password key in plain sight, but looking like an encrypted  string.

On a clear disk you can seek forever

Re: please help, Can the project be connect with Microsoft sql server?

CDB wrote:

Thinking about how to make the connection secure, by which I take you mean the connection string is obfuscated in some way, I came up with the following idea. Not tested, so have no idea if it would work, and is probably more complicated than needs be! smile

1. Create a SQLite database that has just one table and for security about 5 fields.
..... Field 1 contains an encrypted form of the connection path details.
..... Field 2 ........   contains the encryption key spread across one or more fields, the other fields  contain dummy information and or the password
.

2. Before opening the SQL Server connection as demonstrated above, read in the SQLite database contents and perform the deincryption.

3. Parse the results into the actual  db_settings:='Provider=SQLOLEDB;Server=localhost;database=DatabaseName;UID=UserName;PWD=password';
 
To make it more secure, a function could be written that actually changes the encryption key (some sort of randomise function) each time the program is run, This could be placed on the MainForms OnClose event where the new encrypted path and key can be written back to the database. I assume the SQL Server DB would be disconnected at this point as well.

I've probably over thought this far more than is necessary, but if it worked, this idea could also be used for other settings in the ini file such as column widths in tablegrids etc.

The other option would be to have the encryption or password key in plain sight, but looking like an encrypted  string.

Only to prevent that your software will be unsafe and funny for security features. Not as "support".

Will not realy work
- .dcu file is a scripted code. Easy to reverse. even a full compiled .exe can be reveresd by some freeware reverser.
- encryption with RC5 has some issues with sqlite text storing. The character table is not constant utf, so it can happen that you can not decrypt what you have encrypted.
- it is allways a bad idea to store connection passwords in a sourcecode, ini file or database without a propper encryption and other safety elements.

Except script kiddis and beginner in programming others will break the security before breakfast just as a morning exercise.

Before you ask: MVD is abandon by the last statement of Dmitry. There are solutions possible, but I support only active compiler. Search for me in other forums and you will see how to make it. (have fun searching).

Re: please help, Can the project be connect with Microsoft sql server?

to secure your software...
delete script.pas and Pack Scripts folder ,Graphics.dll with software.exe using enigma virtual box.

Re: please help, Can the project be connect with Microsoft sql server?

I see a lot of examples to read data from MSSQL SERVER but none on INSERTS, did anyone ever tried to insert data from MVD to MSSQL SERVER?

10 (edited by tcoton 2024-03-02 20:31:53)

Re: please help, Can the project be connect with Microsoft sql server?

Alright, I got it covered... this works to insert data from MVD to SQL Server:

procedure PROCEDURE_NAME_Button1_OnClick (Sender: string; var Cancel: boolean);
 var
  Conn : Variant;
  db_settings,Col1,Col2 : String;
begin

  // ***  create database objects ****
   Conn :=createoleobject('ADODB.Connection') ;


   //*** set database connection parameters ***
  db_settings:='Provider=SQLOLEDB;Server=********;Database=********;UID=********;PWD=*******************;

  //*** Open connection to database ***
  Conn.Open(db_settings);

  //*** Insert data into SQLServer Database ***
  // Format of VALUES() ==> if source field contains only numbers: ‘+<name_of_field.txt>+’   if source fields contains varchar ==> ‘’’+<name_of_field.txt>+’’’   (triple single quotes)
  // One Conn.Execute() per insert.


Conn.Execute('INSERT INTO [TABLE_NAME] (Col1,Col2) VALUES ('+FORM_NAME. FIELD_NAME1.Text+','''+FORM_NAME. FIELD_NAME_2.Text+''')');  //example using fields as datasource
//Conn.Execute('INSERT INTO [TABLE_NAME] (Col1,Col2) VALUES (111,’’VARCHAR_TEXT’’)’); //example using direct data (double single quotes only for varchar text)
     //Always close connection after each transaction
     Conn.Close;
end;