MySQL via PHP-Tunnel (über eigene libmysql.dll)

[mschaefer]

Datenbank-Zeichensatz
Hier hilft das Einstellen einer anderen Codepage, auch über PHPMyAdmin.

Code:

SET SESSION CHARACTER_SET_RESULTS =latin1;
SET SESSION CHARACTER_SET_CLIENT =latin1;

Mehrbenutzerfähigkeit für Internetanwendungen
Hier gib es de Möglichkeit den aktuell angemeldeten Benutzer in die Tabelle einzutragen. Zum Beispiel um späte eine Logik zu entwickeln, die Löschvorgänge auf selbst angelegte Datensätze beschränkt. MySQL bietet hier die USER()-Funktion an: SELECT CURRENT_USER();.

Um den aktuellen Benutzer abzufragen kann man folgende Abfrage schicken:

Code:

SELECT SUBSTRING_INDEX(USER(),'@',1);

Man kann dann eine Log-Tabelle anlegen um Eintragungen zu protokolieren.

Code:

CREATE TABLE app_log
(
    t       TIMESTAMP,
    user    CHAR(16),
    host    CHAR(60)
);

Hier kann man mit einem Insert-Statement aktuellen USER und HOST eintragen. TIMESTAMP hat dabei die aktuelle Datum/Zeit-Kombination.

Code:

INSERT INTO app_log
    SET user = SUBSTRING_INDEX(USER( ),'@',1),
        host = SUBSTRING_INDEX(USER( ),'@',-1);

Oder man baut einen Trigger der den aktuellen User im Insert mit aufnimmt:

Code:

create database dbtest; 
use dbtest; 
grant all privileges on dbtest.* to 'testuser'@'localhost'; 
create table test (creator varchar(30), modificator varchar(30), text varchar(100)); 
create trigger ins_test before insert on test for each row set new.modificator=current_user();

Jetzt kann verhindert werden, dass ein anderer User den eigenen Datensatz löscht. Das geht über einen Before-Delete Trigger:

Code:

DROP TRIGGER IF EXISTS users_anon;
delimiter |
CREATE TRIGGER users_anon BEFORE DELETE ON users
  FOR EACH ROW BEGIN
    -- Prevent deletion 
    IF (OLD.modificator <> user() ) THEN
      -- This raises an exception and prevents deletion
      INSERT INTO users VALUES (OLD);
    END IF;
  END;

Man kann auch noch eine Audit/Log Tabelle führen und sehen wer gelöscht hat:

Code:

 -- before delete trigger
     CREATE OR REPLACE TRIGGER employee_before_delete
      BEFORE DELETE
          ON MyTable
          FOR EACH ROW
      DECLARE
          v_username varchar2(10);
      BEGIN
          -- Find username of person performing the DELETE on the table
          SELECT user INTO v_username
          FROM dual;
          -- Insert record into audit table
          INSERT INTO table_audit (id,      delete_date,deleted_by )
                              VALUES (:old.id, sysdate, v_username );
     END;

Ziel der Geschichte ist eine Tabelle mehrbenutzerfähig im Internet verwalten zu können.
Etwas Feinabstimmung ist hier wohl noch drin.

Grüße in die Runde

[janvanbogget]

Ich habe ein Problem: wenn ich den umzug Delphi antrag gestellt, um einen PC, wo Delphi nicht installiert ist, dann ist es nicht mehr zu existieren. Ich habe aber den zugehörigen dll's dbxmys.dll und libmysql.dll in das arbeitsverzeichnis und im System32 ordner geschrieben.

Wenn ich zu diesem Host verbinden möchten bekomme ich eine Fehlermeldung: DBX Error: Warning

Wenn die anwendung auf dem entwickler-Station funktioniert und auf einem PC ohne Delphi nicht, dann nehme ich an, dass es noch einige notwendigkeit, auf dem PC, wo kein delphi installiert ist, aber was? Kann mir jemand ein tipp / hint geben ?

ps: ich kann nicht Deutsch schreiben (Hilfe von Google Übersetzer)


Vielen Dank für jede Hilfe

[janvanbogget]

Dear,


I have tested your application.

I have compiled your source code with Delphi 2007 Win32, and it works great !

But your dll libmysql.dll is a 32-bits dll, and i work With Lazarus 64 bit ..
So my application can not work with the 32-bits dll.

My question is this : You have make a 64-bits dll ? If you have make this, is it possible that i can use this dll ?

Best Greetings, Jan Van Bogget ( Belgium ) [e-mail]

[sippytom]

Hi,
I am English so sorry I cannot ask this question in German.
I have tested your Zeos addition and am very happy.
One problem I have is in inserting some characters into a database.
Here is a demo from your program which will show the error.
INSERT INTO nodes VALUES (4, 2, 'AA:=1234; bbA');

The error occurs around the ":=".
Has this something to do with the way the data is sent to the php file.
If so,is their a way around the error.
Thanks in advance

OR. are their any other sql tunnel routines available.

Tom Duncan
Australia

[Sir Rufo]

Hi Tom,

please show us the exact and real code enclosed in

Code:

[DELPHI][/DELPHI]

tags.
Do not show us look alike code.

I supect, that your code looks something like

Delphi-Quellcode:

var
  stmt : string
begin
  stmt := 'INSERT INTO nodes VALUES (4, 2, 'AA:=1234; bbA');';
end;

[sippytom]

Sorry for that.
Yes that is the code.
It seems as though the error is the ":" or "=" or the ";" which causes the error.

Tom

[Sir Rufo]

Syntax Highlight will tell you what is wrong

wrong code

Delphi-Quellcode:

var
  stmt : string
begin
  stmt := 'INSERT INTO nodes VALUES (4, 2, 'AA:=1234; bbA');';
end;

right code

Delphi-Quellcode:

var
  stmt : string
begin
  stmt := 'INSERT INTO nodes VALUES (4, 2, ''AA:=1234; bbA'');';
end;

You simply have to double the quotes inside the string

[sippytom]

Thanks, will do.
I was using the Delphi quotedstr function.
Will change to double quotes.
Tom

[Sir Rufo]

Zitat:

Zitat von sippytom (Beitrag 1302910)

Thanks, will do.
I was using the Delphi quotedstr function.
Will change to double quotes.
Tom

You should change the whole to use parameters in your queries. Just take a google search for that

[teosuper]

The answer is not complete. I agree to the quotes but the assignment ":=" is Pascal, not SQL. So you have to change that to "=".

Sorry.. Neither English nor German so I forgot what the ":" is called :-) Good luck!

[Sir Rufo]

Zitat:

Zitat von teosuper (Beitrag 1302919)

The answer is not complete. I agree to the quotes but the assignment ":=" is Pascal, not SQL. So you have to change that to "=".

Sorry.. Neither English nor German so I forgot what the ":" is called :-) Good luck!

If you agree to the double quotes why do you claim anything inside the string value

Delphi-Quellcode:

'AA:=1234; bbA'

to be Pascal or SQL? It is just a string and can contain anything you like and will not be handled by Pascal or SQL. It is just a value

[teosuper]

Ja, stimmt schon.. Nicht gut hingeschaut..

Indeed.. the whole part in quotes is inserted in a field in the database so the Pascal ":=" does not matter nor affect anything.

[sippytom]

Just did some tests and still got the error

Code:

DROP TABLE IF EXISTS nodes;
CREATE TABLE nodes (
  node_id int,
  parent_id int,
  bez TEXT,
  CONSTRAINT PK_nodes PRIMARY KEY (node_id),
  CONSTRAINT FK_nodes_parent FOREIGN KEY (parent_id) REFERENCES nodes (node_id)
) ENGINE=InnoDB;

INSERT INTO nodes VALUES (1, NULL, 'A');
INSERT INTO nodes VALUES (2, 1, 'AA');
INSERT INTO nodes VALUES (3, NULL, 'B');
INSERT INTO nodes VALUES (4, 2, "AA=1234; bbA");

Still have error on the bottom line.
So not everything enclosed in quotes is passed as a string? :oops:

Tom

[teosuper]

Hello Tom,

MySQL will not accept double quotes I guess. The double quotes are only valid from within Delphi.
Your SQL statement should work if you quote the string in the last line just like the others.

[sippytom]

Hi,

Single quotes is how I had it before. Still with the error.
Maybe it has something to do with the transfer of data between zeos and the tunnel php file.

Tom

[teosuper]

I executed this in Heidisql without errors:

DROP TABLE IF EXISTS nodes;
CREATE TABLE nodes (
node_id int,
parent_id int,
bez TEXT,
CONSTRAINT PK_nodes PRIMARY KEY (node_id),
CONSTRAINT FK_nodes_parent FOREIGN KEY (parent_id) REFERENCES nodes (node_id)
) ENGINE=InnoDB;

INSERT INTO nodes VALUES (1, NULL, 'A');
INSERT INTO nodes VALUES (2, 1, 'AA');
INSERT INTO nodes VALUES (3, NULL, 'B');
INSERT INTO nodes VALUES (4, 2, 'AA=1234; bbA');

Maybe very stupid question but if the sql I post does not work for you, what happens if you replace the ";"? That could very well be a delimiter in the PHP tunnel..

[sippytom]

I am using an sql tunnel demo from the 1st post.
I just need a way to connect to a mysql database using tunneling.
I have not done a check but is Heidisql a sql tunnel?

I am using Delphi 7

[teosuper]

HeidiSql is a tool like phpMyadmin.

I have a few general suggestions: first is to replace the ";" with some different character. If that works, at least you know what the issue is.

A quick work around: if you use stringreplace to replace ";" when posting and another stringreplace when retrieving the data, your problem should be worked around.

[sippytom]

Thanks, yes I think I will test various characters that I can set on the way to the database and reset on the way out.

Tom