SQL Databases

[Grincheux]

How is it possible from a local PC to open a SQL database stored into a web server ?
Can I connect to the server using winsock and then call mySQL API to open the databases ?
On my PC I can address the local SQL databses using "localhost" but in that case I cannot use the "localhost".

Can someone help me ?

Thanks

[fearless]

First you connect to the database, and then perhaps validate a login if required - in the below example i validate against a phpbb2 forum that is stored on my site - the username and password hash are just examples and dont work tho ;-)

Once you are connected then you can use mysql_query or mysql_real_query to perform MySQL statements. You can then:

Code Select Expand

invoke mysql_use_result,MySQLconnection

which returns a MySQLresult (defined in .data section as: MySQLresult dd mysql_res) then you can

Code Select Expand

invoke mysql_fetch_row,MySQLresult then something like this in a loop to fetch each row and do something with the data - in the snippet below it fetches teams to fill into a listview, ebx points to the team_id column and ebx+4 the team name.

Code Select Expand

mov ebx, eax
mov row, eax
.WHILE eax != NULL
; Add to listview the teams

invoke wsprintf,addr LVItemText,CTEXT("%s"),dword ptr [ebx]
Invoke atodw, Addr LVItemText
mov team_id, eax

Invoke ListViewInsertItemParam, hLV_Teams, row, team_id

invoke wsprintf,addr LVItemText,CTEXT("%s"),dword ptr [ebx+4]
Invoke ListViewInsertSubItem, hLV_Teams, 1

Invoke ListViewSetItemIcon, hLV_Teams, row, 0

;Invoke ListViewSetItemParam, hLV_Teams, row, team_id

invoke mysql_fetch_row,MySQLresult
mov ebx, eax
mov row, eax
.ENDW

Code Select Expand

MySQLConnect PROTO :DWORD, :DWORD, :DWORD, :DWORD, :DWORD
MySQLDisconnect PROTO :DWORD
MySQLLogin PROTO :DWORD, :DWORD, :DWORD, :DWORD, :DWORD, :DWORD, :DWORD

Code Select Expand

MySQLConnect PROC hWin:DWORD, szWebAdddress:DWORD, szAdminName:DWORD, szAdminPass:DWORD, szDatabase:DWORD

Invoke mysql_init,addr mysql
mov MySQLconnection, eax
.if eax==NULL
; insufficient memory

.endif
invoke mysql_real_connect,MySQLconnection,szWebAdddress,szAdminName,szAdminPass,szDatabase,0,NULL,0
.if eax==NULL
; error connecting to server
invoke mysql_error,MySQLconnection
mov MySQLerror, eax
invoke wsprintf,addr szError,addr szFmtError,eax
invoke MessageBox,hWin,addr szError,CTEXT("Error"),MB_ICONERROR
;.else
; invoke MessageBox,hWin,CTEXT("Success: Connected to Database"),CTEXT("Connected"),MB_OK
.endif
mov eax, MySQLconnection
ret

MySQLConnect endp

Code Select Expand

MySQLDisconnect PROC hWin:DWORD

invoke mysql_close,MySQLconnection
ret

MySQLDisconnect endp

Code Select Expand

MySQLLogin PROC hWin:DWORD, szUsername:DWORD, szPassword:DWORD, szUserField:DWORD, szPassField:DWORD, szDatabase:DWORD, szUserTable:DWORD
LOCAL szMySQLUsername[255]:BYTE
LOCAL szMySQLPassword[255]:BYTE
LOCAL szPassHash[255]:BYTE

Invoke MD5CreateHash, szPassword, Addr szPassHash ; used for comparing pass hash in phpbb2 forum table on webserver

; Build select string
Invoke szCopy, Addr szNull, Addr MySQL_Select_String
;Invoke szCatStr, Addr MySQL_Select_String, Addr szFullQuote ; "
Invoke szCatStr, Addr MySQL_Select_String, Addr MySQL_Select ; SELECT
Invoke szCatStr, Addr MySQL_Select_String, szUserField ; *userfield*
Invoke szCatStr, Addr MySQL_Select_String, Addr szComma ; ,
Invoke szCatStr, Addr MySQL_Select_String, Addr szSpace ;
Invoke szCatStr, Addr MySQL_Select_String, szPassField ; *passfield*
Invoke szCatStr, Addr MySQL_Select_String, Addr MySQL_Select_From ; FROM
Invoke szCatStr, Addr MySQL_Select_String, Addr szEscape ; `
Invoke szCatStr, Addr MySQL_Select_String, szDatabase ; *database*
Invoke szCatStr, Addr MySQL_Select_String, Addr szEscape ; `
Invoke szCatStr, Addr MySQL_Select_String, Addr szFullStop ; .
Invoke szCatStr, Addr MySQL_Select_String, Addr szEscape ; `
Invoke szCatStr, Addr MySQL_Select_String, szUserTable ; *usertable*
Invoke szCatStr, Addr MySQL_Select_String, Addr szEscape ; `
Invoke szCatStr, Addr MySQL_Select_String, Addr MySQL_Select_Where ; WHERE
Invoke szCatStr, Addr MySQL_Select_String, szUserField ; *userfield*
Invoke szCatStr, Addr MySQL_Select_String, Addr szSpace ;
Invoke szCatStr, Addr MySQL_Select_String, Addr szEqual ; =
Invoke szCatStr, Addr MySQL_Select_String, Addr szSpace ;
Invoke szCatStr, Addr MySQL_Select_String, Addr szFullQuote ; "
Invoke szCatStr, Addr MySQL_Select_String, szUsername ; *username*
Invoke szCatStr, Addr MySQL_Select_String, Addr szFullQuote ; "
Invoke szCatStr, Addr MySQL_Select_String, Addr MySQL_Select_And ; AND
Invoke szCatStr, Addr MySQL_Select_String, szPassField ; *passfield*
Invoke szCatStr, Addr MySQL_Select_String, Addr szSpace ;
Invoke szCatStr, Addr MySQL_Select_String, Addr szEqual ; =
Invoke szCatStr, Addr MySQL_Select_String, Addr szSpace ;
Invoke szCatStr, Addr MySQL_Select_String, Addr szFullQuote ; "
Invoke szCatStr, Addr MySQL_Select_String, Addr szPassHash ; 34973298478237473927492374897
Invoke szCatStr, Addr MySQL_Select_String, Addr szFullQuote ; "

; SELECT username, user_password FROM `newworld_phpb1`.`phpbb_users` WHERE username = "Somedude" AND user_password = "1946858FB0B830BFDA570E568694A95F"

Invoke szCatStr, Addr MySQL_Select_String, Addr szNull
invoke mysql_query,MySQLconnection,addr MySQL_Select_String
.if eax!=0
; SELECT statement is wrong
invoke mysql_error,MySQLconnection
invoke wsprintf,addr szError,addr szFmtError,eax
invoke MessageBox,hWin,addr szError,CTEXT("Error"),MB_ICONERROR
mov eax, FALSE
ret
;.else
; invoke MessageBox,hWin,CTEXT("Success: Fetched Username and Password"),CTEXT("Query"),MB_OK
.endif
invoke mysql_use_result,MySQLconnection
.if eax==NULL
; Error
invoke mysql_error,MySQLconnection
invoke wsprintf,addr szError,addr szFmtError,eax
invoke MessageBox,hWin,addr szError,CTEXT("Error"),MB_ICONERROR
mov eax, FALSE
ret
.endif
mov MySQLresult,eax

invoke mysql_fetch_row,MySQLresult
mov ebx,eax
.IF eax != NULL
invoke wsprintf,addr szMySQLUsername,CTEXT("%s"),dword ptr [ebx]
invoke wsprintf,addr szMySQLPassword,CTEXT("%s"),dword ptr [ebx+4]
;PrintString szMySQLUsername
;PrintString szMySQLPassword
;invoke MessageBox,hWin,Addr szMySQLPassword,Addr szMySQLUsername,MB_OK
invoke mysql_free_result,MySQLresult
mov eax, TRUE
.else
;invoke MessageBox,hWin,CTEXT("Failed to login"),CTEXT("Failed"),MB_OK
invoke mysql_free_result,MySQLresult
mov eax, FALSE
.endif

ret

MySQLLogin endp

[Grincheux]

Thank you Fearless, I thought you were dead.

This is a very good tuto.

It seems to be exactly what I was expecting. I don't know if the web hosting company will allow this.

[fearless]

Your welcome :D

Still alive and busy coding away on my little projects - doing documentation mainly at the mo.

Check with provider, if you have Cpanel access on your site you should be able to create sql databases, assign username and passwords for access. myPhpAdmin usually comes with Cpanel as well and this will give you the ability to create tables, fields etc. I also use EMS MySQLManager utility to access mysql databases remotely to edit data.