Length of accessToken in UserConnection table

[handiekar]

Is it possible to know how can I increase the length of accessToken field in the UserConnection table.

As I'm working on the Windows Live plugin and getting the following error while trying to authenticate with the API.

WARNING: Exception while handling OAuth2 callback (PreparedStatementCallback; SQL [insert into UserConnection (userId, providerId, providerUserId, rank, displayName, profileUrl, imageUrl, accessToken, secret, refreshToken, expireTime) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; Value too long for column "ACCESSTOKEN VARCHAR(255) NOT NULL": "'EwAgAq1DBAAUlb... (728)"; SQL statement:

insert into UserConnection (userId, providerId, providerUserId, rank, displayName, profileUrl, imageUrl, accessToken, secret, refreshToken, expireTime) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [22001-159]; nested exception is org.h2.jdbc.JdbcSQLException: Value too long for column "ACCESSTOKEN VARCHAR(255) NOT NULL": "'EwAgAq1DB... (728)"; 

SQL statement:
insert into UserConnection (userId, providerId, providerUserId, rank, displayName, profileUrl, imageUrl, accessToken, secret, refreshToken, expireTime) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [22001-159]). Redirecting to /signin

Thanks,
Sachin

[habuma]

Well, the schema for the UserConnection table is defined in the main Spring Social project at src/main/resources/org/springframework/social/connect/jdbc/JdbcUsersConnectionRepository.sql. There is nothing (except for tests and some of the sample projects) that directly use this SQL file directly and it is intended as a starting point and convenience for those wanting to create the UserConnection table. In other words, if you are writing an app that uses Spring Social, it would be your responsibility to create the UserConnection table, either by using that SQL file or using a similar CREATE TABLE.

So, I have 2 questions for you: (1) How did you create the table in the first place and (2) what app are you running where you see this error? My guess...tell me if I'm wrong...is that you're using Spring Social Showcase (or one of the other examples) as a testbed for the Windows Live work you're doing. If that's the case, then you'll want to change the MainConfig.java class to reference a custom SQL file (one you create that has a larger column size) instead of JdbcUsersConnectionRepository.sql.

I'd also be curious to know what changes you make to support the token length (e.g., what is the token length you need for Windows Live?).

And...is there a GitHub project for your work that I can reference? I'd love to be able to share your work with the world on the Spring Social home page and perhaps via Twitter, if you don't mind sharing it. In the spirit of open-source and GitHub, you might even get some folks pitching in to help you with your work.

[willers]

I have a simillar problem, when spring social encrypts the accessToken property
the lenght of the encrypted value is 289, and the accessToken column of UserConnection table
is configured as varchar(255).

I'm trying to authenticate by facebook, and the textEncryptor bean is configured as below:

<bean id="textEncryptor" class="org.springframework.security.crypto.encrypt.Encryptors"
		factory-method="text">
		<constructor-arg value="${social.security.encryptPassword}" />
		<constructor-arg value="${social.security.encryptSalt}" />
</bean>

Have you seen a problem like this before? Or could this be a mistake in the configuration?

[habuma]

Access tokens could possibly be of any length, although most providers keep them relatively small. Facebook access tokens, for instance are around 96 characters long and even when encrypted can easily fit within the 255 character field prescribed for the UserConnection table.

But yes, if the tokens granted by your provider are longer then the encrypted form could exceed 255 characters. In that case, you'll need to alter your UserConnection definition to accommodate whatever size you need. Spring Social doesn't really care how big that field is and the JdbcUsersConnectionRepository.sql only serves as a starting point...you are welcome to define the fields to whatever length you need.

Out of curiosity, what provider are you working with that issues such large tokens?

[willers]

hmmmmmmmmm... then that's normal. Thanks for your response.

I'm using the facebook provider. In my test, accessToken has 113 characters and encrypeted it has 289.

[habuma]

Interesting. My FB access tokens are only 96 characters in length...wonder why yours are 113. What kind of encryption are you using that has them ending up as 289 characters? (I'm asking so that I can replicate your efforts and try it out myself.)

In any event, as I said before you are certainly able to extend the length of that column to match whatever you need. That one single JdbcUsersConnectionRepository.sql just serves as a guideline for creating the table. The table and column names must remain the same, but the other details can be tweaked as necessary. Spring Social never uses that SQL file directly except in tests and some of the samples.