Data Conversion (String to varchar(32))

Do you have questions regarding other database enginges (not MySQL) -- ask here!

Moderators: egami, macek, gesf

Post Reply
landi
php-forum Active User
php-forum Active User
Posts: 388
Joined: Thu Mar 15, 2012 3:59 pm

Wed Feb 17, 2016 2:22 am

Hi,
I don't do SqlServer, but in other databases such as oracle, Varchar can be bytes or chars, where chars are unicode (2 bytes per char). Char causes Microsoft Access all sorts of headaches. I don't know if you can, or if you need to declare your varchar as byte, but if you can it is worth a shot.

Also, posting pseudo code doesn't help you as we can't find the subtle mistakes that us programmers make all the time, so I suggest you reduce the problem to two single SELECT statements and post the actual code. Obviously substitute for the username + password in your connect. Also remove any columns from the queries that aren't needed to show the problem.

Reducing the problem to it's simplest form not only makes it easier for us to understand it, it often helps you find the real issue (like a date column that, believe it or not, M$ can't handle certain values of).

Also, your use of 'global' looks a bit suspect - it shouldn't be used on the declaration, but on the usage. If you are starting out, you may want to consider classes and other structured techniques for passing data around in an application.

Further, assembling sql statements using string concat is a real bad idea because of SQL Injection. e.g. "select from x where key = ' " . $recid . ' " (I've added some spaces to emphasise the quotes) To illustrate the problem, Imagine this SQL statement:

Code: Select all

$sql = "select user_id from very_secure_table where very_secret_password = '" . $user_input ."'";
Looks safe enough huh? The user can only get the record where their password matches. Now try setting user_input like this and re-run the sql. It will always return a record:

Code: Select all

$user_input = "x' or '1' = '1";
$sql = "select user_id from very_secure_table where very_secret_password = '" . $user_input ."'";
Again I don't know Sql Server but other databases have 'parameters' that allow you to do something like this:

Code: Select all

// Sql statement using bind parameters:
$sql = "select user_id from very_secure_table where very_secret_password = :bv_user_input";
// bind user input to parameters:
some_function_like_bind(':bv_user_input', $user_input); // Add the parameter value
// execute:
And lastly, I'm sure there are many business objects examples out there doing something similar, so it may help to check those out.

Hope that helps,
-A

landi
php-forum Active User
php-forum Active User
Posts: 388
Joined: Thu Mar 15, 2012 3:59 pm

Wed Feb 17, 2016 8:38 am

Ultimately, I don't think it is the select statements that are the issue here, I get the feeling that it is more about the way PHP stores the data. The value retrieved from the first SQL statement
In general, I've never had to manipulate varchar data in and out of php variables. So I expect your example to work. But, I can't tell because you haven't posted the code.

I suggest you avoid select * for this test and use 'select single-column', with hard coded keys. Your code should be 10 lines or so. If you can't the reduce problem to a few lines of code and post them, then sorry I can't really help :)

-A

Post Reply