Improving Database Security in Web-based Environments
Francesco Di Tria, Ezio Lefons and Filippo Tangorra
Dipartimento di Informatica, Università degli Studi di Bari Aldo Moro, Via Orabona 4, 70125 Bari, Italy
Keywords: SQL Injection, Authentication, Authorization, Web Application, Architecture.
Abstract: In web applications, databases are generally used as data repositories, where a server-side program interacts
with a Database Management System (DBMS), retrieves content, and dynamically generates web pages.
This is known as a three-layer architecture, that is widely exposed to database threats. The attacks are
usually performed through the injection of SQL code in the forms of the web applications, exploiting the
dynamic construction of SQL statements. So, the database security relies on the quality of the code and the
controls done by the web developer in the application level. In this paper, we present a solution for the
improvement of security of databases accessed by web applications. The security is based on a user
modelling approach that completely relies on the authorization mechanism of DBMSs.
1 INTRODUCTION
In Web-based environments, a three-layer
architecture is commonly adopted, composed of a
back-end and a front-end. In the back-end, we find
two main servers: the web server and a database
server (or DBMS). Currently, the web server hosts
static web pages and/or scripts, which are server-
side programs interacting with a DBMS in order to
access the database and to provide dynamic and on-
demand content.
A server-side program (or web application)
needs to store a plain-text account to make
connections to the database. As a consequence,
anyone who has granted a physical access to the
server’s file system is able to view the password
used for the authentication to the DBMS. In this
sense, the account of a database user (even the
administrator, in some cases) is publicly accessible.
Furthermore, the account is usually formed of a
couple of username and password. This account is
used for all the connections to the database,
independently from the user profile. For the sake of
simplicity, it is the web application that accesses the
database, not the user. So, it is not possible for the
DBMS to know who is really using the web
application and to apply its own authorization
mechanism (Gertz and Jajodia, 2007).
For example, if two roles, namely manager and
employee, are defined in the web application, with
the policy that an employee cannot access the
financial data, then the DBMS is not able to know if
the current user that is querying the database
(through the web application) is logged in as a
manager or an employee. Therefore, the DBMS
cannot adopts its security model in the case of
attacks to the database coming from illegitimate
users (Bertino and Sandhu, 2005).
Clearly, in these environments, the database
security widely depends on the web developer’s
ability and the program code quality. The developer
has to bear all the controls necessary to protect the
database. If the application is not well-developed,
then the database is exposed to a class of attacks
known as SQL injection that exploits the possibility
to dynamically construct dangerous SQL statements
(Roy et al., 2011). Therefore, the threats to database
security are often attributable to the web developer’s
negligence or inexperience. To this reason, a web
application assessment is useful to detect
vulnerabilities (Huang et al., 2003; Xiang Fu et al.,
2007). Another possible solution is the adoption of
systems able to analyze the traffic towards a DBMS
and report anomalies (Pinzón et al., 2010; Rietta,
2006).
In this paper, we present an approach to improve
the database security where the security model does
not depend on the application level, but it is
incorporated in the DBMS level. In fact, DBMSs
always include strong authorization mechanisms
that, in web-based environments, are de facto
unutilized. The proposed security model is based on
Tria, F., Lefons, E. and Tangorra, F.
Improving Database Security in Web-based Environments.
DOI: 10.5220/0005646901930200
In Proceedings of the 2nd International Conference on Information Systems Security and Privacy (ICISSP 2016), pages 193-200
ISBN: 978-989-758-167-0
Copyright
c
2016 by SCITEPRESS Science and Technology Publications, Lda. All rights reserved
193
a mapping of web applications users, who access
resources like dynamic web pages, to database users,
who must be granted privileges to execute the views
that generates the contents of those web pages.
In the following, we refer to a typical WAMP
(Windows, Apache, MySQL, PHP) or LAMP
(Linux, Apache, MySQL, PHP) environment to
illustrate examples.
The paper is structured as follows. Section 2
introduces an overview of the threats to the database
security. Section 3 presents our approach based on a
mapping of web application users to corresponding
DBMS users. Then, Section 4 reports some real
scenarios and practical examples. Finally, Section 5
concludes the paper with our remarks.
2 OVERVIEW OF DATABASE
VULNERABILITY
The first issue is related to the user modelling.
DBMSs are provided with both an authentication
and an authorization mechanism. The authentication
deals with the identification of the user (who is the
user) and is usually based on passwords, that is, the
so-called something you know paradigm. The other
paradigms are something you are, such as biometrics
parameters, and something you have, like electronic
badges.
On the other hand, the authorization is devoted to
check whether an authenticated user is enabled to
access a given resource (what the user can do). The
authorization is usually done with privileges. So,
DBMSs have a profile of each registered user (see
Fig. 1) such that they are able to know the user
identity and the database objects (such as
procedures, views or tables) which he/she is allowed
to access.
Figure 1: DBMS user modelling.
For these reasons, when users interact with a
Web Application, the authentication and the
authorization mechanism of the DBMS might not be
applied.
In fact, such applications are configured with a
unique account that is used to access the system
database (see Fig. 2), which stores both data and
user profiles.
As a consequence, the Web Application knows
the user identity, but the DBMS does not (Ben
Natan, 2005). From the DBMS’s point of view, the
database user is always the web application itself,
usrWA in this example.
Figure 2: Web Application user modelling.
The second issue is related to the configuration
file. The account (that always includes a username
and a password of a database user) is used by the
application and it is stored as a plain text, that is, it is
not encrypted. Anyone, who has a physical (or a
remote) access to the computer where the
application resides, is able to read and copy the
configuration file. Storing a plain-text password is
the worst threat for any system.
The third important issue is related to dynamic
and embedded SQL. Web applications include and
create SQL instructions by concatenating text strings
and user inputs. This leads to several security issue,
known as SQL-injection attacks. According to this
kind of attack, malicious users can insert specific
sequences of characters when filling a form of a web
page, determining the generation of dangerous SQL
instructions (Boyd and Keromytis, 2004).
In (Halfond et al., 2006), the authors report a
complete list of the possible goals that can be
reached by malicious users performing SQL-
injection attacks.
Bypassing Authentication. Users performing this
kind of attack aim at bypassing the application
ICISSP 2016 - 2nd International Conference on Information Systems Security and Privacy
194
authentication mechanism, by obtaining access to
the system without providing a valid password.
This may happen when the web application
executes no user input validation or uses a weak
query like the following in order to authenticate the
users:
select * from users where
username=’$username’ and
password=’$password’
where $username and $password are the
variables containing the user login input. In fact, if
$username contains a malicious string like “’
or 1=1 #, then the query that is dynamically
generated by the program is the following
select * from users where
username=’’ or 1=1 #and
password=’$password’.
(The # symbol is used to comment code). The last
SQL statement is a tautology and the user is
authenticated without any password.
Furthermore, a threat to the authentication may
be preceded by a port-scanning, a process devoted to
discover and exploit potentially vulnerable services
on a network host (Vasserman et al., 2009).
Extracting Data. This is the most common and the
most dangerous attack, as it allows the visualization
of (possible sensitive) data. Also this threat is based
on alterations of legitimate SQL statements, for
example adding union clause. As an example, if the
application generates a query like
select projectName, projectDescr
from projects where
projectResp=’$projectManager’
to extract the projects assigned to a given manager
and the manager’s name is a search parameter, then
a user who accesses the search page can insert the
string
union select username, password
from user
to view all the projects and the user accounts. This
threat can be mitigated using encrypted password
and, even better, using privileges: the user used to
query the database must not be granted of read
privilege on the users table (i.e., the table storing
user profiles).
To summarize, the web applications should be
configured with different database users having
different privileges. So, when querying the database,
the web application should connect to the DBMS
using the user with the lowest privileges. On the
other hand, when performing system maintenance,
the application may connect to the DBMS using a
user with higher privileges.
In the following, we focus on these two kinds of
attack, because are strictly related to dynamic and
embedded SQL statements.
To complete the overview about threats to web
applications, other security issues due to network
protocol or DBMS vulnerability are:
Performing Denial of Service. This attack is devoted
to destroy a service, preventing other legitimate
users to access the database (Jiangtao et al., 2009).
Performing Privilege Escalation. Users performing
this kind of attack aim at reaching database
administrator privileges (the so-called root
privileges) by exploiting bugs in the DBMS.
3 USER MODELLING
Our approach is based on a mapping of web
application users to database users, as shown in Fig.
3. So, when a new user is registered in the web
application, we do what follows.
A new record in the users table is inserted. This
record stores the username chosen by the user. A
default role, the one with the minimum privilege,
is assigned to him/her. This information is
managed at application level. At this level no
password is necessary anymore.
A new password-protected user is created in the
database, with the same username chosen for the
application level.
Figure 3: Web Application vs. DBMS users.
The security model at application level
Improving Database Security in Web-based Environments
195
establishes the resources accessible for each user on
the basis of his/her role. So, the username is used by
the application only to show the correct web pages
to a logged user.
When a user requires a resource, by accessing a
web page, the application executes a call to a
procedure stored in the database. To make the
connection to the database, the application must use
the account provided by the user (and not the
predefined one, stored in the configuration file). Of
course, the user must provide username and
password only the first time he/she accesses a
resource, as the account can be temporarily stored in
session variables.
At this point, the procedure executes the query, if
the user has the correct privileges, and returns a
well-formed HTML object, as a table, for example.
So, this approach is also based on a mapping of
resources (web pages) at application level and
corresponding resources (procedures) at DBMS
level. The procedures dynamically create one or
more SQL statements and return HTML tables. The
main strategy is that a user who accesses a web page
showing data must be granted of the necessary
privileges by the database to read and/or modify
those data.
In Fig. 4, there is depicted an example of a web
application whose authorization model aims only at
visualizing the right resources for each user.
However, the database security is entrusted to the
DBMS’s authorization mechanism that is based on
privileges.
In the example, John and Mary, whose
usernames are, respectively, john and mary, are web
application users. John has the student role, while
Mary has the teacher role. On the basis of his/her
role, a user can access a predefined set of resources.
A student can access the courses list, while a teacher
can access both the courses and teachers lists. So,
the web application will show (the link for) the
courses list to John, and it will show both (the links
for) the courses and teachers lists to Mary.
In order to display the necessary data, the
resource (that is, the web page) needs to invoke a
procedure to retrieve the content. To do so, the
courses procedure has to generate and execute the
courses view. At this point, if John does not have the
read (R) privilege on the courses view, he will not
able to visualize the content. For the same reason,
Mary needs the read privilege on both the courses
and teachers views in order to display the data.
So, if a user, fraudulently or due to a web
developer’s error, succeeds in accessing a denied
resource (at application level), he/she will not able to
Figure 4: DBMS resources vs. application resources.
access the related data anyway, because he/she does
not have the necessary privileges on that view and/or
table.
The benefits of these approach are the following:
the web application does not need to store any
plain-text password to access the database;
for each connection to the database, the personal
database user account is used instead of a
common database account assigned to the
application;
the web application must deal only with the
correct creation of the user interface (leading
only to the resources the users is allowed to
access according his/her role);
no SQL injection is possible, as the query are
created at DBMS level by the procedures using
parameters and not at application level on the
basis of string concatenation;
assuming to correctly assign privileges on
databases objects, users will never be able to
view sensitive data, even by performing union-
based SQL injection attacks or exploiting bugs in
the application;
the web application does not need to know
anything about the logical schema of the
underlying database; so, a complete
independence of the application layer from the
data layer is ensured; and
the web application invokes a database resource
(not a query) to retrieve the contents.
ICISSP 2016 - 2nd International Conference on Information Systems Security and Privacy
196
4 SCENARIO EXAMPLES
In this Section, we show some real scenarios
according to the data that are exchanged between the
two layers. Figure 5 illustrates the data that are
currently exchanged in web-based environments.
Figure 5: Traditional data flow.
Users performs authentication in the web
application (WA) providing both username and
password managed at application level. (The
authentication is usually performed in the login page
solely on the basis of the user profile stored in the
database, then the logged user identifier is stored in
session variables and used to access private pages).
In order to extract data, the web application uses
its own database account (wa account¸ in the figure)
to make a connection to the DBMS. When
connected, the application generates a dynamic
query, using the search parameters, and executes it
on the database. The database returns a recordset
that is fetched by the application and, as an example,
transformed in HTML tables.
In our approach, the two layers exchange data in
a different way, as shown in Fig. 6.
The user provides his/her account in the login
page of the web application. Nonetheless, the
application ignores the password and uses only the
username to display the right web pages to the user,
whereas each username is associated to a role and
each role can access one or more resources. The
complete account is stored in session variables, as
this must be passed to the DBMS when connecting
to execute queries.
To read data, the application calls a procedure
with a well-defined interface (specifying the input
and output parameters). The procedure eventually
generates a dynamic query (or uses predefined
views) and, if the provided user account has the
correct privileges on views and/or tables, it returns
an HTML table. To summarize, the application uses
the personal account provided by the user in order to
interact with the DBMS.
Figure 6: Proposed data flow.
4.1 Accessing Resources
In this subsection, we show an example of the login
phase performed using our approach. As an
example, this can be used to generate the home page
of the user, showing the correct resources he/she is
allowed to access.
We can assume the following relational schema
that manages users and the related resources.
resources(id, resource,
description). The table storing the list of
resources (web pages) managed at application
level
roles(id, description). The list of
roles defined for users.
users(username, role,
registration_date). The table storing
user profiles, without passwords.
access(role, resource). The table the
associates a user role to the allowed resources.
We report in Code 1 the server-side program.
Code 1. Access phase.
Input:
$username username,
$password password,
$db_host database IP address,
$db_name database name.
Output:
Web page with the list of the resources the user can
access
1.
$db = mysqli_connect($db_host, $username,
$password, $db_name);
Improving Database Security in Web-based Environments
197
2. $query="call resources('$username',@b)";
3. $result = mysqli_query($db,$query);
4. $row = mysqli_fetch_array($result);
5. print $row['b'];
This program first reads the account provided by
the user in the login form, then uses it to connect to
the DBMS (lines 1), and invokes a procedure (lines
2-3) which returns a well-formed HTML tables. At
last, the HTML table is extracted just in a single
statement, i.e., without a do-while cycle (line 4), and
displayed (line 5).
Then, we report the procedure (see Procedure 1)
stored in the DBMS that retrieves the resources to
which a user can access on the basis of the role
associated to his/her username.
This procedure generates a query (line 5) and
uses a cursor to fetch the rows (lines 8-15). Each
row is inserted into a string containing HTML tags
(line 14). To this end, the MySQL concat function is
used to create a well-formed HTML table. However,
a different text format, such as XML or JSON, can
be used.
It is worth noting that in line 5 a dynamic query
is created without a string concatenation, but on the
basis of the parameters, username in this example.
To summarize, the first benefit of this approach
is that the authentication cannot be bypassed using
SQL-injection. Indeed, the web application does not
has to generate any SQL statement and it does not
manage any password neither.
Procedure 1. Access phase.
Input:
username nickname of the user at application and
database level,
Output:
b HTML table
1.
CREATE PROCEDURE
`security`.`resources` (in username
varchar(50), out b text)
2. BEGIN
3. DECLARE no_more_rows BOOLEAN;
4. DECLARE a varchar(50);
5.
DECLARE cur1 CURSOR FOR
select resources.resource
from resources inner join access on
resources.id=access.resource
inner join roles on
access.role=roles.id
inner join users on
roles.id=users.role
where users.username=username;
6.
DECLARE CONTINUE HANDLER FOR
NOT FOUND
SET no_more_rows = TRUE;
7. set b := '<table border=1>';
8. OPEN cur1;
9. r: LOOP
10. FETCH cur1 INTO a;
11. IF no_more_rows THEN
12. LEAVE r;
13. END IF;
14.
select
concat(b,'<tr><td>',a,'</td></tr>')
into b;
15. END LOOP r;
16. select concat(b,'</table>') into b;
17. select b;
18. CLOSE cur1;
19. END
Of course, if the user provides a wrong username
and/or password, then a DBMS connection error will
be triggered.
Moreover, since the password is provided by the
user and is stored in session variables, it cannot be
stolen by anyone who may be able to access the web
directory (for example, using a brute force attack to
the FTP server).
4.2 Search Function
As another testing scenario, now we show that this
approach is also resistant to union-based SQL
injections executed by appending union-clauses in
fields of traditional search forms used for generating
reports.
The server-side code is similar to that illustrated
in Code 1. So, it is not shown again. For the same
reason, we do not report the whole procedure stored
in the DBMS, but only the dynamically generated
query.
If the user has the role necessary to access the
teaching courses list, then he/she will be able to
reach the web page that contains the search form,
which represents an application-level resource.
The only search parameter is year and this will
be sent to a procedure (see, Procedure 2) in order to
generate a query. In detail, the query aims at
retrieving the teaching courses available in a given
academic year (line 2).
To access data, the user needs also the privileges
on both courses and courses_year tables that are
DBMS-level resources.
ICISSP 2016 - 2nd International Conference on Information Systems Security and Privacy
198
Procedure 2. Search.
Input:
year year of the academic course,
Output:
b HTML table
1.
CREATE PROCEDURE
`security`.`teaching` (in year varchar(100), out
b text)
2.
select courses.name from
courses inner join courses_year on
courses.id=courses_year.id
where courses_year.year=year;
In case of an attempt of injecting malicious code
in the search field by the following statement
2015 union select username from
users
then the procedure will create a syntactically wrong
SQL instruction equivalent to the following
select courses.name from courses
inner join courses_year on
courses.id = courses_year.id
where courses_year.year =
‘2015 union select username
from users’.
Notice that the data type of the input parameter is
varchar in this ad hoc example (line 1). Had we
used the correct data type, that is year, we would
have obtained a data truncation error.
Moreover, assuming to create a syntactically
correct SQL instruction, the user will not be able to
view the data, because s/he does not have the
necessary privileges on the users table.
5 CONCLUSIONS
In this paper, we have presented an approach to
improve database security that is a current threat to
web-based environments. In fact, the system security
is always entrusted to web developers who must
implement all the necessary input validation in order
to ensure system invulnerability.
Our approach is based on a replication of user
accounts at DBMS level such that the personal
account of a given user is used to connect to the
DBMS instead of a common password, stored as
plain text. In this context, web resources, such as
web pages, are mapped to database resources, such
as procedures managing further resources that are
external views.
The assumption is that a user may pass the
application’s controls, but that user will never be
able to access data anyway, if he/she is not granted
the necessary privileges.
Future work is devoted to a benchmark between
the traditional data flow and our approach, as we
believe that the creation of an HTML object is faster
when using a cursor to scan a table at DBMS level
than fetching rows at application level.
REFERENCES
Ben Natan, R., 2005. Implementing Database Security and
Auditing. Elsevier Digital Press.
Bertino, E., and Sandhu, R., 2005. Database security -
Concepts, Approaches, and Challenges, IEEE
Transactions on Dependable and Secure Computing,
vol. 2, issue 1, pp. 2-19.
Boyd, S. W., and Keromytis, A. D., 2004. SQLrand:
Preventing SQL Injection Attacks, Applied
Cryptography and Network Security, Lecture Notes in
Computer Science, vol. 3089, pp. 292-302.
Gertz, M., and Jajodia, S., 2007. Handbook of Database
Security: Applications and Trends, Springer, 1 edition.
Halfond, W. G. J., Viegas, J., and Orso, A., 2006. A
Classification of SQL Injection Attacks and
Countermeasures, Proceedings of the IEEE
International Symposium on Secure Software
Engineering, Arlington, VA, USA.
Huang, Y-W, Huang, S-K., Lin, T-P., and Tsai, C-H.,
2003. Web Application Security Assessment by Fault
Injection and Behavior Monitoring, Proceedings of the
12th International Conference on World Wide Web,
Budapest, Hungary, pp. 148-159.
Jiangtao Li, Ninghui Li, XiaoFeng Wang, Ting Yu, 2009.
Denial of Service Attacks and Defenses in
Decentralized Trust Management, International
Journal of Information Security vol. 8, issue 2, pp.89-
101.
Pinzón, C., De Paz, J. F., Bajo, J., Herrero, A., and
Corchado, E., 2010. AIIDA-SQL: An Adaptive
Intelligent Intrusion Detector Agent for Detecting SQL
Injection Attacks, 10th International Conference on
Hybrid Intelligent Systems (HIS), pp. 73-78.
Rietta, F. S., 2006. Application Layer Intrusion Detection
for SQL Injection, Proceedings of the 44th Annual
Southeast Regional Conference, Melbourne, Florida,
pp. 531-536.
Roy, S., Kumar Singh, A., and Singh Sairam, A., 2011.
Analyzing SQL Meta Characters and Preventing SQL
Injection Attacks Using Meta Filter, 2011
International Conference on Information and
Electronics Engineering, IPCSIT vol. 6, pp. 167-170.
Vasserman, E. Y., Hopper, N., and Tyra, J., 2009.
SilentKnock: Practical, Provably Undetectable
Improving Database Security in Web-based Environments
199
Authentication, International Journal of Information
Security, vol. 8, pp. 121-135.
Xiang Fu, Xin Lu, Peltsverger, B., Shijun Chen, Kai Qian,
Lixin Tao, 2007. A Static Analysis Framework for
Detecting SQL Injection Vulnerabilities, 31st Annual
International Computer Software and Applications
Conference, Beijing, pp. 87-96.
ICISSP 2016 - 2nd International Conference on Information Systems Security and Privacy
200