10

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.

enter image description here

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 ?

  • Can you run a query? – David דודו Markovitz Feb 11 '17 at 11:09
  • @DuduMarkovitz Yes I can run querries. – yoyo_fun Feb 11 '17 at 11:33
  • The fact that this statement works for some and not for others seems to suggest that the specific environment matters. Probably @joanolo was right when he said that these queries run in the browser. Different people use different browsers, some possibly with additional extensions installed. – Andriy M Feb 11 '17 at 13:17
  • 2
    "Undefined function in expression." is an Access error message so seems like server side they are using Jet – Martin Smith Feb 11 '17 at 14:45
  • @MartinSmith What is Jet in this context ? Where can I read more about it ? – yoyo_fun Feb 11 '17 at 17:05
14

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
  • thanks for the answer but unfortunately nothing works on this website w3schools – yoyo_fun Feb 11 '17 at 12:06
  • Try the last: SELECT sqlite_version(), and you'll have your answer. – joanolo Feb 11 '17 at 12:09
  • 1
    It looks like the version they have actually completely runs on your browser. Look at github.com/kripken/sql.js – joanolo Feb 11 '17 at 12:23
  • is there any way to make sure this is the case. Does this in-browser version of SQL language has any way of checking its version or that is in fact this SQL engine ? – yoyo_fun Feb 11 '17 at 12:57
7

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.

  • do you know why they took this decisision ? Is there any difference between SQL in the browser and SQLite ? – yoyo_fun Feb 11 '17 at 17:51
  • @yoyo_fun: en.wikipedia.org/wiki/Web_SQL_Database has some pointers. Or you could ask this as an independant question on dba.se – Daniel Vérité Feb 12 '17 at 14:57
  • this is a very good idea indeed :) – yoyo_fun Feb 12 '17 at 23:12
5

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.

enter image description here

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)

enter image description here

  • Thanks for the detailed answer. Can you tell me how did you get the first screenshot ? What is the editor that you are using? Is it an extension for a web browser ? – yoyo_fun Feb 11 '17 at 19:49
  • @yoyo_fun the developer tools built in to Firefox – Martin Smith Feb 11 '17 at 19:54
3

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     
2

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":

enter image description here

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.