Using session variable in sql

Hi

Please help with the follwing:
This is my testpassword.aspx file:

Expand|Select|Wrap|Line Numbers

 
<%@ Page Language="C#" Debug="true" %>

<%@ Import Namespace="System" %>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Configuration" %>

<%@ Import Namespace="System.Xml.Linq" %>

<%@ Import Namespace="System.Data.SqlClient"%> 

<%@ Import Namespace="System.Data.OleDb"%> 
 
<html>

<head>

<title>Session Page 2</title>

</head>

<body><p>ASP.NET C# session page 2</p>

<p>first name: <%=Session["FirstName"]%></p>

<p>Password: <%=Session["Password"]%></p>
 
<% 

  Response.Write("Connecting to db");

  OleDbConnection Myconnection= null;

  OleDbDataReader dbReader = null;

  Myconnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\WebSites\WebSite1\besa.accdb");

  Myconnection.Open();

  OleDbCommand cmd = Myconnection.CreateCommand();

  cmd.CommandText ="SELECT password FROM user WHERE username='"+Session["FirstName"]+"' ";

  dbReader = cmd.ExecuteReader();         

  while (dbReader.Read())

   {

   Response.Write(dbReader.GetString(1));

   }

  dbReader.Close();

  Myconnection.Close();

  Response.Write(" end of prog");

%>            
 
</body>

</html>

I am, getting the follwing error:

Expand|Select|Wrap|Line Numbers

 
Syntax error in FROM clause.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
 
Exception Details: System.Data.OleDb.OleDbException: Syntax error in FROM clause.
 
Source Error:
 
Line 23:   OleDbCommand cmd = Myconnection.CreateCommand();

Line 24:   cmd.CommandText ="SELECT password FROM user WHERE username='"+Session["FirstName"]+"' ";

Line 25:   dbReader = cmd.ExecuteReader();         

Line 26:   while (dbReader.Read())

Line 27:    {
 
Source File: c:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\WebSites\WebSite1\testpassword.aspx    Line: 25

I am new to ASP and not sure how to use the session var in SQL.

Thank you for any help and advise.

Nov 8 '10 #1

Subscribe Post Reply

4603

Frinavale

9,735

Expert Mod 8TB

You are accessing session correctly and it looks like what you posted should work.

I recommend that you change your approach though.
Instead of concatenating user input into your SQL command (which leaves you open to a SQL injection attack)...consider using the OleDBCommand.Parameters property to supply user input into the SQL command.

-Frinny

Nov 8 '10 #2

Oralloy

988

Expert 512MB

I would suggest that you mask out the logic from your page here, and just display the SQL that you are generating.

Then, go test the SQL in the native interface. I don't see an SQL error, but that doesn't mean that there isn't one.

Also, if you don't get a hit on Session["FirstName"], your SQL may degenerate into SELECT password FROM user WHERE username='' which may be why you're having problems.

Finally, Frinny is exactly right - you should never construct SQL by means of string concatenation.

There are at least four good reasons why:

Users can enter malicious values, which terminate one bit of your SQL, and then append theirs right on the tail end by use of a semicolon. This is a very common threat that you should be aware of.

There are many characters in strings, which must be specially mapped, so that they will work with your database. This list is not the same for all databases. Use of the parameter interface abstracts this problem, effectively removing it.

Time and debug cost. If you have to specially code character string translations, then I can guarantee that you'll make mistakes, drop quotes, and fail to translate special characters. Guaranteed - it's just a mater of when.

Quotes are not your friend. You will forever be massaging string variables to double quotes. This takes time to do correctly every time, and I guarantee that if you don't do the translation, you'll have unexpected, live data with those quote marks in it. Usually at a very inconvienent time.

Nov 8 '10 #3

Tinus

Thank you.

Expand|Select|Wrap|Line Numbers

 
<%@ Page Language="C#" Debug="true" %>

<%@ Import Namespace="System" %>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Configuration" %>

<%@ Import Namespace="System.Xml.Linq" %>

<%@ Import Namespace="System.Data.SqlClient"%> 

<%@ Import Namespace="System.Data.OleDb"%> 
 
<html>

<head>

<title>Session Page 2</title>

</head>

<body><p>ASP.NET C# session page 2</p>

<p>first name: <%=Session["FirstName"]%></p>

<p>Password: <%=Session["Password"]%></p>

<% 

  Response.Write(Session["FirstName"]);

  Response.Write("Connecting to db");

  OleDbConnection Myconnection= null;

  OleDbDataReader dbReader = null;

  Myconnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\WebSites\WebSite1\besa.accdb");

  Myconnection.Open();

  string FirstName = Session["FirstName"].ToString();

  OleDbCommand cmd = Myconnection.CreateCommand();

  //cmd.CommandText = "SELECT password FROM user WHERE username='" +Session["FirstName"]+ "'";

 // dbReader = cmd.ExecuteReader();         

  /* 

   while (dbReader.Read())

   {Response.Write(dbReader.GetString(1));}

    dbReader.Close(); 

  */

  Myconnection.Close();

  Response.Write("end of prog");

%>            
 
</body>

</html>

This gives

Expand|Select|Wrap|Line Numbers

 
ASP.NET C# session page 2
 
first name: user123
 
Password: pass123

user123Connecting to dbend of prog

So the Session["FirstName"] does return a result.

I will have a look at the OleDBCommand.Parameters property.

Nov 9 '10 #4

Oralloy

988

Expert 512MB

Well, so much for hopeing that it was something easy.

I'm as much at a loss at the moment as you are. Hopefully your research will yield good fruit.

Nov 9 '10 #5

Tinus

Hi

I have found the sollution.
User and password are reserved words in ms access so .

Expand|Select|Wrap|Line Numbers

 
string Firstname=Session["FirstName"].ToString();

  OleDbCommand cmd=Myconnection.CreateCommand();

  cmd.CommandText="select [user].[password] from [user] where [user].[username]='"+Firstname+"'";

Nov 22 '10 #6

Similar topics

class -session variable

by: | last post by:

Hi, I want to do things this way: I have a bunch of stuff that I want to keep track of while a user is connected to the site. Maybe 50 little peices of information. So I know I can make 50...

ASP / Active Server Pages

Session Variable gets truncated and drives me crazy

by: doltharz | last post by:

Please Help me i'm doing something i though was to be REALLY EASY but it drives me crazy The complete code is at the end of the email (i mean newsgroup article), i always use Option...

ASP / Active Server Pages

Simple Postback using Session Variable

by: Scott | last post by:

I'm trying to set a session variable after displaying a form, then capture the Session variable on postback. For some reason, the below code always returns the form, not the "Step 2" results part....

ASP / Active Server Pages

Shared Variable Vs. Session Variable

by: John Kraft | last post by:

Hi all, My question is more of a phylisophical one here, but I am wondering what the difference is (effectively and performance wise) between using a shared variable/static variable and using a...

ASP.NET

Trouble with using Session state

by: Greg Linwood | last post by:

I'm having difficulty understanding Session state in ASP.Net. It's almost embarrassing asking this as I've been using ASP since it was first released & it really shouldn't be this hard to use -...

ASP.NET

Session Variable Madness

by: Todd | last post by:

Here's one that has been stumping people: I'm writing in ASPX with VB.NET On the login page I set the entered usename text to a session variable....

ASP.NET

Using Session variable in ASP.NET page

by: Stimp | last post by:

When a user logs into my site, I create a cookie with their UserID, but I wish to also create a Session variable to track their "UserType". I do this as follows: Session("UserType") = "admin"...

ASP.NET

Transferring Object from Session Variable to New Class Object???

by: Don Miller | last post by:

I am using a Session variable to hold a class object between ASP.NET pages (in VB). In my constructor I check to see if the Session variable exists, and if it doesn't, I create one and populate it...

ASP.NET

Session Variable naming conventions.

by: Control Freq | last post by:

Hi, Not sure if this is the right NG for this, but, is there a convention for the variable names of a Session variable? I am using .NET 2.0 in C#. I am new to all this .NET stuff, So, any...

ASP.NET

use SESSION variable?

by: Twayne | last post by:

Hi, If ever a newbie wants to know how much he has to learn yet, he only has to look here<g>!! ANYway: PHP 5.2.5; XP Pro SP2+, local Apache Server My actual question is: How do I get a...

PHP

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

Career Advice

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

Microsoft Access / VBA