How to check what Database Engine is installed on the DataBase server that I have acces to run queries on?

Question

I want to check what type of sql is running on a Datasase server that I can access. I only have access to a web interface and a list of tables.

Through the interface I can run queries on the tables that are present on a list.

How can I get more information about the server and the version that the server is running. I have no idea about the IP or the PORT that the server is running.

I want to know if the server is MySQL, Mircosoft SQL Server, Oracle SQL, Postgre SQL or other sql server.

The website that I am talking about is this one: w3schools.com SQL editor.

EDIT 2: although for some the command select sqlite_version() works for me it does not work. This is the screenshot of the response.

EDIT 3 : On Chromium Browser the command is working properly. However on Firefox Browser the command did not work.

I also mention that I am on running Linux.

What do you think could be the reason why on Firefox and on Chrome I get different results ?

Answer 1

I assume that your web interface lets you issue SQL commands. If so, you can use:

SELECT version();

PostgreSQL

If you are on a PostgreSQL database, you get a response similar to

PostgreSQL 9.6.1 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit

MySQL

If you are on a MySQL database, the answer looks like

5.7.12-log

Oracle

If you are on an Oracle database, you'll get an error message:

ORA-00923: FROM keyword not found where expected

(The ORA-xxxx tells you you're on Oracle). To find out which specific version, try:

SELECT banner as "oracle version" from v$version

You'll get a response like:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0  Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Microsoft SQL Server

If you're on MS SQL Server, the response will also be an error, and look like:

'version' is not a recognized built-in function name.

In that case, you can try:

SELECT @@version ;

And you'll get, as a response, something looking like:

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   
    Oct 28 2016 18:17:30   
    Copyright (c) Microsoft Corporation  
    Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor) 

SQLite

If you're on a SQLite database, you'll get an error message when you try SELECT version():

 could not prepare statement (1 no such function: version)

In that case, you can try:

 SELECT sqlite_version()

And the response will look like:

3.14.0

Answer 2

This online SQL editor uses the Web SQL Database, meaning SQL embedded in the browser. It's easy to recognize if you look at their JS source code at http://www.w3schools.com/w3Database.js and compare the API calls to the W3C spec of Web SQL, for instance how they open the database:

w3Database = window.openDatabase('W3SchoolsDemoDatabase', '1.0',
           'W3SchoolsDemoDatabase', 2 * 1024 * 1024);

So there is no database server, it's really the browser that executes the SQL, with the SQL engine that your browser has choosen to use (assuming it has the SQL capability to start with), for which sqlite makes a lot of sense.

(EDIT: not strictly true because according to Martin Smith's answer, the JS submission code falls back to a server-side MS-Jet engine when the browser does not support Web SQL).

Note that the W3C has decided to abandon the SQL-in-the browser concept in 2010, it's now obsolete.

Answer 3

As stated in the other answers it depends on your browser.

If your browser does not have native support for Web SQL Database then it ends up submitting back to the server.

This uses Jet as can be seen by running

SELECT X FROM Foobar

The Microsoft Jet database engine cannot find the input table or query 'Foobar'. Make sure it exists and that its name is spelled correctly.

which means proprietary Access extensions like TRANSFORM work when accessing the page from Firefox (but this will fail in Chrome)

Answer 4

This answer was given before the OP added a reference to w3schools.com

---

If you can run a query, start with this:

select version();        -- PostgreSQL  e.g.: PostgreSQL 9.6beta2, compiled by Visual C++ build 1800, 64-bit                                                                                                                                            

select version();        -- MySQL       e.g.: 5.7.11-log                                                                                                                                                                                                

select @@version;        -- SQL Server  e.g.: Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)   Apr 29 2016 23:23:58   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows 7 Enterprise 6.1 <X64> (Build 7601: Service Pack 1)         

select * from v$version; -- Oracle      e.g.: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production                      

select sqlite_version(); -- Sqlite:     e.g.: 3.15.1     

Answer 5

For the specific case of the SQL engine used by the page at W3Schools:

Safari, Chrome and Opera Browsers

If you use the Safari Browser (I tested on Mac OS X 10.12) the page is using SQLite, which seems to be built into the browser itself. When you press the "Run SQL" button, it doesn't use any external resource. Further reverse engineering reveals that the page is initializing the database by means of the following JavaScript code:

function w3WebSQLInit() {
    var w3DBObj = this;
    w3Database = window.openDatabase('W3SchoolsDemoDatabase', '1.0', 'W3SchoolsDemoDatabase', 2 * 1024 * 1024);
   ...
}

window.openDatabase means the code is using a Web SQL Database. This was defined by the W3C a few years ago, and was put in practice by Opera, Safari and Chrome (AFAIK), but not by FireFox (or Explorer). All three browsers seem to have implemented the "Web SQL Database" by embedding different versions of SQLite within themselves.

At this point, the W3C page claims:

"Beware. This specification is no longer in active maintenance and the Web Applications Working Group does not intend to maintain it further."

I tested a Chrome browser (56.0.2924.87 version, on Windows 10), and it seems to use SQLite 3.10.2. Opera (version 12.15, on Windows 10), uses SQLite 3.7.9.

Opera is clear enough in their "about":

And Safari Client-Side Storage and Offline Applications Programming Guide also mentions that

Beginning in Safari 3.1 and iOS 2.0, Safari supports the HTML5 JavaScript database class The JavaScript database class, based on SQLite, provides a relational database intended for local storage of content that is too large to conveniently store in cookies (or is too important to risk accidentally deleting when the user clears out his or her cookies).

I didn't look into Chrome... but my guess is clear.

FireFox, Edge and Internet Explore Browsers

If you use the FireFox browser (I tested on Mac OS X 10.12); the same W3School page behaves in a radically different way. It does not perform the queries on the browser, but sends requests to their server. On the server side, I think it is actually using an MS Access database-like. One way to check it is by checking the (standard) information_schema.

If you issue the following query (which, in a standards-compliant database would give the list of tables accessible to the user):

SELECT * FROM information_schema.tables;

You get the following (too telling) error response:

Could not find file 'C:\Windows\SysWOW64\inetsrv\information_schema.mdb'.

If the software is looking for a .MDB file, that means they're using the Microsoft Jet Database Engine (or something equivalent) on the server side, and they are using Access .MDB format files, which correspond to Access versions up to and including 2003. (Access 2007 and later versions use, by default, .ACCDB format files).

Microsoft Internet Explorer 11 behaves the same way (on Windows 10); and so does Edge (on Windows 10).