Using session variable in sql

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Tinus
    New Member
    • Jul 2010
    • 30
    #1

    Using session variable in sql

    Hi

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

    Code:
    <%@ 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:

    Code:
    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.
    Last edited by jhardman; Nov 8 '10, 12:56 PM. Reason: Accidentally posted in the classic asp forum. Moved to asp.net
    Tags: None
    • Frinavale
      Recognized Expert Expert
      • Oct 2006
      • 9749
      #2
      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)...consi der using the OleDBCommand.Pa rameters property to supply user input into the SQL command.

      -Frinny

        Comment

        • Oralloy
          Recognized Expert Contributor
          • Jun 2010
          • 988
          #3
          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:
          1. 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.
          1. 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.
          1. 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.
          1. 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.
          Last edited by Oralloy; Nov 8 '10, 09:14 PM. Reason: Accidentally posted incomplete message.

            Comment

            • Tinus
              New Member
              • Jul 2010
              • 30
              #4
              Thank you.
              Code:
              <%@ 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
              Code:
              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.Pa rameters property.

                Comment

                • Oralloy
                  Recognized Expert Contributor
                  • Jun 2010
                  • 988
                  #5
                  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.

                    Comment

                    • Tinus
                      New Member
                      • Jul 2010
                      • 30
                      #6
                      Hi

                      I have found the sollution.
                      User and password are reserved words in ms access so .
                      Code:
                      string Firstname=Session["FirstName"].ToString();
  OleDbCommand cmd=Myconnection.CreateCommand();
  cmd.CommandText="select [user].[password] from [user] where [user].[username]='"+Firstname+"'";

                        Comment

                        Previous template Next
                        Working...