How do you connect your own websites to SQL Server?

[LordFede]

I've seen many different ways, but now I wonder what practices are website deveolpers using at the moment of creating a connection to game servers.

Since PHP's mssql driver has been obsolete for a while, and sqlsrv driver works only for windows' hostings, what if you want to host your website on a linux based server?

 

[KarLi]

even linux webservers provide MSSQL extension enabled, depends mostly on what webserver host u got.. then the rest are easy to connect just to 1433 and enable tcp/ip and open ports etc from sql config manager and firewall

 

[LordFede]

even linux webservers provide MSSQL extension enabled, depends mostly on what webserver host u got.. then the rest are easy to connect just to 1433 and enable tcp/ip and open ports etc from sql config manager and firewall

MSSQL functions are obsolete in php7

You must be registered to see links

(luckily).
SQLSRV extension runs only on windows hosts, and ODBC actually is such a pain in the butt

 

[KarLi]

okay well i dont know alot about web stuff but, this ?

You must be registered to see links

 

[LordFede]

okay well i dont know alot about web stuff but, this ?

You must be registered to see links

I wonder if FreeTDS is compatible with muonline binary data (inventories) and if someone ever implemented this on a muonline website :hmmmm2:

 

[nevS]

Isn't it dangerous to let a (PHP-)website directly connect to the Mu server database?
Imho it's safer to setup a webservice (e.g. as REST-Service) which just exposes the required data by accessing the database. The webserver would just connect to this webservice. To implement such a webservice, you wouldn't use PHP and obsolete drivers, but better suited stuff like .NET

 

[LordFede]

Isn't it dangerous to let a (PHP-)website directly connect to the Mu server database?
Imho it's safer to setup a webservice (e.g. as REST-Service) which just exposes the required data by accessing the database. The webserver would just connect to this webservice. To implement such a webservice, you wouldn't use PHP and obsolete drivers, but better suited stuff like .NET

You'll end up interacting with server database's and connecting to the same server anyway.

Can you elaborate a bit more about the approach you're suggesting? How would you implement it?

 

[nevS]

You'll end up interacting with server database's and connecting to the same server anyway.

Can you elaborate a bit more about the approach you're suggesting? How would you implement it?

Sure, at the end you'll connect to the game server database, too. However, with the additional layer a hacker might not be able do things like SQL injection to your gameserver database through your website.

How I would implement it:
1. A server application (.NET or .NET Core) which provides the required data as a (rest-)service:
- connects to the gameserver's database
- exposes the required data (should not be sensitive information) as a service (e.g. very easy with the Nancy Framework)
- if you need to manage logins, you could offer an authentication mechanism as a service
- limit the access, so that only the website-server can connect to this service.
- Here you can also implement some caching, so you dont hit the database at every web-request
2. In your PHP-Code you connect to the (rest-) service to get your required data. I'm not a PHP expert, but I would be surprised if this wouldn't be possible.

 

[LordFede]

I get the point.

That's a good practice, I can't afford that at the moment. The only way I can code the API is through Node.js and sql drivers for node are not good at all when dealing with inventory BLOBs.

 

[cryy]

If your under php 7, microsoft has SQLSRV extensions for linux also.

 

[LordFede]

If your under php 7, microsoft has SQLSRV extensions for linux also.

I think you're talking about this one

You must be registered to see links

Known Issues

• User defined data types and SQL_VARIANT.
• Binary column binding with emulate prepare (
  You must be registered to see links
  )
• Linux
  • The following features are not supported with connection pooling:
    • Unicode connection strings
    • sqlsrv_server_info and sqlsrv_client_info return false
    • In certain scenarios a generic error message maybe returned instead of a specific error when pooling is disabled
    • When retrieving data from columns with a data type of XML, varchar(max), nvarchar(max), or varbinary(max) no data maybe returned or the data maybe truncated depending on the length of the data in the source table.

Inventories' data type is varbinary, and they are pretty lengthy. So I think we can't use that for mu db yet.

 

[cryy]

You must be registered to see links

php 7, linux using pdo_sqlsrver driver inventory and warehouse is fully working

 

[LordFede]

You must be registered to see links

php 7, linux using pdo_sqlsrver driver inventory and warehouse is fully working

You mean

You must be registered to see links

driver? That's the link I get from

You must be registered to see links

To be honest, I haven't tried that one yet. But I think its the same than

You must be registered to see links

Now I wonder... If I can use pdo_sqlsrv on linux, I don't need odbc at all, do I?
I mean, I can now just set the dsn like

sqlsrv:Server=111.11.111.111,1431;Database=MuOnlineDB3

 

[cryy]

You must be registered to see links

Version 4.0
They have also linux instructions.

 

[LordFede]

You must be registered to see links

Version 4.0
They have also linux instructions.

I'll take a look then. Thanks for the info!
Edit: Just realized my app is running on CentOS not linux... saddly.

 

[LarvasFaint]

I'll take a look then. Thanks for the info!
Edit: Just realized my app is running on CentOS not linux... saddly.

CentOS is a linux distribution

 

[LordFede]

CentOS is a linux distribution

Yes but no. I'm running CentOS 6, the driver is for Red Hat 7 / CentOS 7

 

[SouLSet]

pdo_dblib

 

[LordFede]

pdo_dblib

Yes, I've seen that one back when I was developing on php5.4. But now, it seems obsolete

If it is not possible to use SqlSrv, you can use the

You must be registered to see links

driver to connect to Microsoft SQL Server and Sybase databases, as the native Windows DB-LIB is ancient, thread un-safe and no longer supported by Microsoft.

 

[RevolGaming]

odbc dont work on remote host, its for fast LAN connection, not for remote..