no result on select query using Npgsql and c#

Question

I tried to search a lot for tutorials on Npgsql and c#. but I couldn't resolve the below problem.

When I run the program, my programs stop and breaks at execute query. and when I try debug and check the return value from the execute reader is empty.

below is the sample code:

string user=textBox1.Text;
NpgsqlConnection dataconnect = new NpgsqlConnection(
  "Server=127.0.0.1;Port=5432;User Id=dbuser;Password=dbpass;Database=dbname;");


string query = "Select USERNAME from helperdata.credentials where USERNAME = "
    + textBox1.Text + " and PASSWORD = " + textBox2.Text;
dataconnect.Open();

NpgsqlCommand command = new NpgsqlCommand(query, dataconnect);


NpgsqlDataReader reader = command.ExecuteReader();

if(reader.Read())
{
    MessageBox.Show("Login Successful");
}
else
{
    MessageBox.Show("Login failed");
}
reader.Close();
dataconnect.Close();

When I try to run the below query in Pgsql it returns the data.

       Select "USERNAME" from helperdata.credentials where "USERNAME" = 'admin'

I am new to Npgsql.

I would also like if someone could provide me some good tutorial sites which provides detail explanation of Npgsql and C#.

Thanks in advance.

If you are expecting only a single result you can use "Getting a single result value using the NpgsqlCommand.ExecuteScalar() method" as per the user manual. npgsql.projects.pgfoundry.org/docs/manual/UserManual.html — ray
– ray, Commented Mar 12, 2014 at 5:11
Also check your new NpgsqlConnection( "Server=127.0.0.1;Port=5432;User Id=dbuser;Password=dbpass;Database=dbname;"); connection string if the db user name / password and db name is correct. — ray
– ray, Commented Mar 12, 2014 at 5:12
@ray thanks for the link. I tried using executescalar() but no luck. — Malav Shah
– Malav Shah, Commented Mar 12, 2014 at 5:37
ok I believe there is something wrong with my query as I figured this error "Additional information: ERROR: 42703: column "username" does not exist" — Malav Shah
– Malav Shah, Commented Mar 12, 2014 at 5:39

MatheusOl · Accepted Answer · 2014-03-12 12:05:57Z

I have identified two problems in your code. The first the usage of uppercase letters on PostgreSQL identifiers. PostgreSQL allows identifiers with other than simple lowercase letter, but only if you quote them.

In fact, you can use, for instance:

CREATE TABLE helperdata.credentials (... USERNAME varchar, ...);

But PostgreSQL will convert it to:

CREATE TABLE helperdata.credentials (... username varchar, ...);

So, to make it really left with uppercase, you have to quote it as following:

CREATE TABLE helperdata.credentials (... "USERNAME" varchar, ...);

And that seems to be the way you have created your table, and the problem with that is that always you refers to that table in a query, you'll have to quote it. So the beginning of your query should be:

string query = "Select \"USERNAME\" from helperdata.credentials ... ";

My recommendation, is to modify your column and table names to don't use such identifiers. For this case you can do:

ALTER TABLE helperdata.credentials RENAME COLUMN "USERNAME" TO username;

The second problem, is the lack of string quotation when you concatenated the username from the textbox into the query. So, you should do something as the following (BAD PRACTICE):

string query = "Select \"USERNAME\" from helperdata.credentials where \"USERNAME\" = '"
    + textBox1.Text + "' and \"PASSWORD\" = '" + textBox2.Text + "'";

There is a huge problem with that, you can have SQL injection. You could create a function (or use one from Npgsql, not sure if there is) to escape the string, or, more appropriately, you should use a function that accept parameters in the query using NpgsqlCommand, which you can simple send the parameters or a use a prepared statement.

Check the Npgsql documentation, and find for "Using parameters in a query" and "Using prepared statements" to see examples (there are no anchors in the HTML to link here, so you'll have to search).

Collectives™ on Stack Overflow

no result on select query using Npgsql and c#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related