SQL Injection on Views

Question

We are using 3-Tier Architecture in ASP.Net.

There are 3 Layers

Presentation
Business
Data Access

The Data Access Layer contains the GetData and ExecuteQuery etc function. What I want to know is that, that want to call the View directly from the Presentation Layer. Is there any chance of SQL injection in calling a view from front-end without using stored procedure?

Presentation Layer (C#)

protected void btnView_Click(object sender, EventArgs e)
        {
            DL obj = new DL();
            DataTable tb = new DataTable();
            string query = "select * from ViewTeacher where FID = " + txtName.Text;

            tb = obj.GetData(query);

        }

DBAccess

public DataTable GetData(string query)
        {
            DataTable datatable = new DataTable();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = query;

            try
            {
                if (cmd.Connection.State != ConnectionState.Open)
                {
                    cmd.Connection.Open();
                }
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(datatable);
                }
            }
            catch (Exception ex)
            {

                throw new ArgumentException(ex.Message);
            }
            return datatable;
        }

Can you provide some code, cause how you call it is (read the function) is where it can go wrong. — Dorvalla, Aug 25 '15 at 07:20
you can call a view directly from presentation layer and it may have sql injection problem based on how you are calling it and hence as requested by the Dorvalla if you provide the code you are using to call the view we can suggest that code causes sql injetion problem or not? — Ramesh Babu, Aug 25 '15 at 07:29

Damien_The_Unbeliever · Accepted Answer · 2015-08-25T07:41:29.917

How are you "calling a view"? If you're running an ad-hoc query of:

SELECT <columns> FROM View WHERE ColumnX = 'Y'

and if that query is being constructed using (potentially) hostile input then yes, of course that can be subject to SQL injection - the whole point of injection is that the attacker can change the nature of the query:

SELECT <columns> FROM View WHERE ColumnX = 'Z'
UNION ALL
SELECT name,0,0,0,0 FROM INFORMATION_SCHEMA.TABLES --'

The attacker isn't limited to just the objects that are present in the original query.

The untrustworthy input in the two above queries was:

and

Z'
UNION ALL
SELECT name,0,0,0,0 FROM INFORMATION_SCHEMA.TABLES --

score 1 · Answer 2 · answered Aug 25 '15 at 07:44

1

As you are writing the query as follows that takes value from a textbox, 100% there is posibility for sql injection.

string query = "select * from ViewTeacher where FID = " + txtName.Text;

answered Aug 25 '15 at 07:44

Ramesh Babu

405
4
10

score 0 · Answer 3 · edited May 23 '17 at 11:58

0

There should be no chance of SQL Injection while calling a view from front end, as views don't take parameters.
reference : can we pass parameter to a view in sql?

edited May 23 '17 at 11:58

Community

1
1

answered Aug 25 '15 at 07:38

Biswabid

1,378
11
26

If there is a hard-coded query in the application for accessing the view then that's not subject to injection - but that's because its a hard-coded query, not because its querying a view. I'm not aware of any API that just takes the name of a view and returns all rows from it (nor sure of the utility of such an API) - in most APIs I'm aware of, you'd need to supply a complete *query* that accesses (and filters) the view. If that's not hard coded, then it's as subject to injection as anything else. – Damien_The_Unbeliever Aug 25 '15 at 07:40
I saw the question before edit only.As per my understanding of question i provided solution. – Biswabid Aug 25 '15 at 07:44
@Biswabid - I also answered before seeing the edit (hence why my query isn't the same as the one now shown in the question) - I could still see the potential for injection. As I say, there's no API I'm aware of that allows you to just specify a view - you have to provide a complete query. – Damien_The_Unbeliever Aug 25 '15 at 07:47

SQL Injection on Views

3 Answers3