GUID can't get from database, and what are they for anyway?

[ebolt007]

So I'm writing an application with MSSql to learn some new syntax and more about different SQL pieces. I have been reading alot about GUID's and how they are good at generating random numbers, but really what's the point? Especially since you can't seem to get them from one table to use in another table, or maybe I just can't? What's the best way to do this? So I have 2 tables, one has the username in it, and another has the password in it, both have a UserId which sets the GUID when the user creates an account using

$GUID = com_create_guid();

then inserting this into both tables. But now when the person logs in, I need to get the username, which I can do fine, but then when I try to get the GUID from the User Table, and then use this GUID to compare against the membership table so I can make sure the password matches up with the username I get the following error.

 

Conversion failed when converting from a character string to uniqueidentifier. (severity 16)

 

using the following code

 

$sql = "SELECT * FROM Users where LoweredUserName = '$username_lower'";
    $sql_result = mssql_query($sql);
    $login_row = mssql_fetch_assoc($sql_result);
          $username = $login_row['LoweredUserName'];
          $GUID = $login_row['UserId'];

$sql3 = "SELECT * FROM Membership WHERE UserId = '$GUID'";
    $sql_result3 = mssql_query($sql3);
    $login_row3 = mssql_fetch_assoc($sql_result3);
   $password = $login_row3['Password'];

 

I've read about using $sql3 = "SELECT cast(UserId as varchar(36)) FROM Membership"; and this gets rid of the error, but then how do I use this to select, because this does nothing from what I can tell. I need to compare the GUIDs from the user table and the membership table. Anyone know how?

Thanks

[trq]

Ints do not need quotes in SQL.

 

$sql3 = "SELECT * FROM Membership WHERE UserId = $GUID";

[ebolt007]

Nope, that didn't work, I get a Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near '='. (severity 15) error when I try that.  Wish it were that simple tho, it has something to do with it being a GUID and a UniqueIdentifier in the SQL Table I believe.

Whoops, meant, when it actually pulls the GUID I get Warning: mssql_query() [function.mssql-query]: message: Unclosed quotation mark after the character string '�h�<��B��\�-�� '. (severity 15)  Not sure what GUID's are even good for if they aren't ints or strings or anything, what are they, and what's the point, how do you match up people and thing in MSSql tables if you can't query them or use them? and I also get

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near '�h�<��B��\�-�� '. (severity 15)  as an error.

[trq]

Post your code.

[requinix]

GUIDs are binary strings with binary data. They aren't VARCHARs or INTs in that you can't just stick them into a query as a regular value.

 

Since PHP doesn't have a GUID type, do a CAST when pulling the values out. When putting the value in you can treat it as a regular VARCHAR string and MS SQL will convert it to a GUID for you. When comparing values inside the database (ie, in the SQL) you don't have to do anything.

 

But you don't need that in this instance. Learn to use JOINs and you won't have to pull the GUID into your code.

SELECT m.* FROM Membership m
JOIN Users u ON m.UserId = u.UserId
WHERE u.LoweredUserName = '$username_lower'

 

Aside: If you're dealing with Membership then use the stored procedures and functions instead. That's what they're for.

[ebolt007]

Awesome thanks requinix that worked like a charm! Thanks sooo much! \

 

I still don't get why people use GUIDs, I've been reading, and they seem more secure and cooler I guess then just incremental ID's especially when using them to create names for photos on upload and links, is that the big reason? I guess it's better to have like a GUID as a link url, then just ?name=3 for an incremental id. and they all look like the same length. And what if I wanted to use these in such an instance, then use the cast on the GUID? But how do I use the cast? I learned that it was basically $sql3 = "SELECT cast(UserId as varchar(36)) FROM Membership"; but that doesn't select anything, does it? I mean I would go more something like $sql3 = "SELECT cast(UserId as varchar(36)) FROM Membership WHERE UserName = '$username'"; if I have username in a variable, but then could I then do

$sql = "SELECT cast(UserId as varchar(36)) FROM Membership WHERE UserName = '$username'";
          $sql_result = mssql_query($sql);
           $login_row = mssql_fetch_assoc($sql_result);
          $GUID = $login_row['UserId'];

to pull the GUID and create a page link for each person basically?

Thanks again for the help!!! Yeah I know some about joins, but geez they're hard to grasp the whole concept all the time on.

 

Edit: And what "stored procedures and functions instead" ugh, back to the old drawing bored of research on this now. Can you point me in a direction tho? Thanks

[requinix]

GUIDs are supposed to be very, very unique. Like unique across everywhere. Not just in your database. But they're long and unwieldy, and don't work so well with non-Microsoft technologies.

In my MS SQL databases I use INT IDENTITY fields instead: they're simple, predictable, and completely portable.

 

I guess it's better to have like a GUID as a link url, then just ?name=3 for an incremental id. and they all look like the same length.

Which of these two URLs do you think look better?

/path/to/script.php?id=3
/path/to/script.php?id=1161E864-99B7-4D6E-A008-F61AA424CB26

 

And what if I wanted to use these in such an instance, then use the cast on the GUID? But how do I use the cast? I learned that it was basically $sql3 = "SELECT cast(UserId as varchar(36)) FROM Membership"; but that doesn't select anything, does it?

It will select things, but you should put an alias on the field so it's easier to get to the value: CAST(...) AS UserId.

GUIDs are actually a sequence of bytes which are very unfriendly to deal with. CASTing to a string type will get you something safe and readable.

 

It I mean I would go more something like $sql3 = "SELECT cast(UserId as varchar(36)) FROM Membership WHERE UserName = '$username'";

Just as good as the other, but they do two different things: the first gets all the records, the second gets just the ones for that user.

 

 

if I have username in a variable, but then could I then do

$sql = "SELECT cast(UserId as varchar(36)) FROM Membership WHERE UserName = '$username'";
          $sql_result = mssql_query($sql);
           $login_row = mssql_fetch_assoc($sql_result);
          $GUID = $login_row['UserId'];

If you add the alias like I mentioned earlier, yes.

 

Edit: And what "stored procedures and functions instead" ugh, back to the old drawing bored of research on this now. Can you point me in a direction tho? Thanks

The short answer is "check the MSDN". I've never dealt directly with the procedures and functions because I've only used Membership data in relation to other data (ie, using them in JOINs with other tables). But if you're querying them directly then there's stuff you can use instead.

Look for a Membership_GetUserByName procedure, for instance. The problem is you often have to deal with a lot more information that is normally kept behind the curtain: application names, side effect UPDATEs, etc.

EXEC dbo.Membership_GetUserByName '/', '$username', GETDATE(), 0

Here's a dump of that function from a database I have. Ignore the "aspnet_" prefix.

/****** Object:  StoredProcedure [aspnet_Membership_GetUserByName]    Script Date: 11/21/2011 15:19:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [aspnet_Membership_GetUserByName]
    @ApplicationName      nvarchar(256),
    @UserName             nvarchar(256),
    @CurrentTimeUtc       datetime,
    @UpdateLastActivity   bit = 0
AS
BEGIN
    DECLARE @UserId uniqueidentifier

    IF (@UpdateLastActivity = 1)
    BEGIN
        -- select user ID from aspnet_users table
        SELECT TOP 1 @UserId = u.UserId
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId

        IF (@@ROWCOUNT = 0) -- Username not found
            RETURN -1

        UPDATE   dbo.aspnet_Users
        SET      LastActivityDate = @CurrentTimeUtc
        WHERE    @UserId = UserId

        SELECT m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
                u.UserId, m.IsLockedOut, m.LastLockoutDate
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE  @UserId = u.UserId AND u.UserId = m.UserId 
    END
    ELSE
    BEGIN
        SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
                u.UserId, m.IsLockedOut,m.LastLockoutDate
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId

        IF (@@ROWCOUNT = 0) -- Username not found
            RETURN -1
    END

    RETURN 0
END

Complicated, yes?

[kicken]

You should be able to pull out a GUID and then use it in another query without any problem.  I do that in various places in our application code with no problems at all.  Just make sure your generating a valid query and that your GUID is formatted correctly when being insert into the query.  The GUID should be formatted such as: '0856B94A-2BF4-476B-AFA2-44F4CCF022AF', optionally including braces on the ends: '{0856B94A-2BF4-476B-AFA2-44F4CCF022AF}'

 

PHP Should receive it as a string without any need to explicitly convert it, and sql server should accept it in string form in a query.

[ebolt007]

I actually like

Which of these two URLs do you think look better?

Code: [select]

 

/path/to/script.php?id=3

/path/to/script.php?id=1161E864-99B7-4D6E-A008-F61AA424CB26

the second one, ie, if you goto facebook or anything, it's all like hidden links, how do I even get rid of the script.php?id= part? Then I can really make a big site, just all GUID, I guess create folders and call to that, but that can get sloppy. Altho it is kinda how I want to do pic's. And Are stored procedures alot faster then regular code? I mean like to update a persons last login, or the amount of times they login, then I guess youd use this as stored procedures, instead of have ing to do an update on the database eachtime right?

[kicken]

how do I even get rid of the script.php?id= part?

 

Using url re-writing, or some other method in which your requests are all routed through a single script which parses the url to grab whatever data is necessary and sends it to another script internally.  In apache the easy way to do this is mod_rewrite.  IIS also has a URL Rewriter module you can install.

 

And Are stored procedures alot faster then regular code?

Stored procedures save a step of the DB engine having to compile the SQL and develop an execution plan.  It can do this ahead of time and then just run it each time the procedure is called.  For simple things like updating a user's last login time or just getting their info, this is not going to make a difference at all.  For more complicated actions which involve several queries in can be beneficial.  For instance, if you have a report which needs to run a number of different queries and combine the results a procedure may help.

 

The main benefit of a procedure is to keep a task isolated in sql and it can make it easier to re-use the same process in multiple places in the code.  Some places also use procedures as a way to enforce certain data requirements by only allowing an application to access the DB via a procedure, not allowing it to access the tables directly.