3

I have to work with somebody's database for a game which sadly has a table named "User" or [dbo][User] and this can not be renamed. Now, I need to access this using PDO in PHP and when I use this query:

$query = "SELECT UserId AS INTUSERID FROM dbo.User WHERE YahooId = 'abcdef'";

it fails, as in nothing is fetched since "User" is a reserved keyword there. From the MS SQL Server I can do this as:

SELECT UserId AS INTUSERID FROM [GameName].[dbo].[User] WHERE YahooId = 'abcdef'

and it works. How should I prepare my query in PHP to make this execute? I have tried to put single quotes around table name but that has no effect. What is the correct way to use 

[GameName].[dbo].[User]

from PHP PDO as the table name ?

Update: This is how I am connecting:

try{
    $conn = new PDO("xxx.rds.amazonaws.com,1150;Database=xyz","admin","password");
    } catch(PDOException $e){
        echo "Connection failed: " . $e->getMessage();
    }
Nikhil Khullar
  • 703
  • 6
  • 21
  • I think this is answered in http://stackoverflow.com/questions/15091734/how-to-escape-strings-in-pdo and http://stackoverflow.com/questions/3716373/real-escape-string-and-pdo – MarkD Jan 25 '14 at 08:11
  • I have tried PDO prepare but those threads seem to be for parameters. In my case, the table name itself is a reserved keyword... – Nikhil Khullar Jan 25 '14 at 08:20
  • @MrVimes Tried backticks... does not have effect. I echoed the query and shows with backticks but does not execute. – Nikhil Khullar Jan 25 '14 at 08:21
  • I deleted my comment as I didn't realize you were using mssql not mysql. Don't the square brackets work? – MrVimes Jan 25 '14 at 08:22
  • i.e. $query = "SELECT UserId AS INTUSERID FROM dbo.[User] WHERE YahooId = 'abcdef'"; I'd test myself but I'm on an ipad :) – MrVimes Jan 25 '14 at 08:24
  • what is your database name ? – CS GO Jan 25 '14 at 08:25
  • The database name is maison and on other tables am able to use dbo.tableName since they are not keywords... @MrVimes square brackets work from SQL Server but not from PHP... :( – Nikhil Khullar Jan 25 '14 at 08:30
  • @NikhilKhullar you are not getting Connection failed msg ? and you are connecting to xxx.rds.amazonaws.com ? – CS GO Jan 25 '14 at 08:33
  • Strange. I'll check back on this question when I get to work. I connect to mssql db with PDO there, and I'm curious about this. Maybe you need to escape the brackets like this \\] but that's just a guess. – MrVimes Jan 25 '14 at 08:36
  • @MrVimes I will wait... and try \] now... Please check on a table named User Thanks ! – Nikhil Khullar Jan 25 '14 at 08:41
  • @MrVimes Escaping with backslashes '\[' and '\]' did not work... :( – Nikhil Khullar Jan 25 '14 at 08:47
  • @MrVimes Thanks for your help... It worked ! For some reason, just for this table I had to give db name too... It worked as this: "FROM [dbName].[dbo].[user]" ... Phew ! – Nikhil Khullar Jan 25 '14 at 09:26
  • @MrVimes Post that as an answer to resolve the question. – Mihai Jan 25 '14 at 09:32
  • Glad it worked :) Wasn't sure whether to answer as my last suggestion was to escape the brackets with a slash. But others finding this question would benefit from seeing an answer so I did. Also, I did get a chance to try on my own code. I didn't need to insert square brackets around the db name or db owner name, just the table name, and it worked. – MrVimes Jan 25 '14 at 10:05

1 Answers1

2

Further to the discussion in the comments I am providing this so that the question has an answer. I thought square brackets would work fine within the php string used to define the query so I tested it out on my own code that connects to MS-SQL using PDO.

So the following should work...

$query = "SELECT UserId AS INTUSERID FROM dbo.[User] WHERE YahooId = 'abcdef'";

Side note - If your YahooId ever comes from a source that can be manipulated by a user (such as $_GET) you should research Prepared Statements with PDO...

$query = "SELECT UserId AS INTUSERID FROM dbo.[User] WHERE YahooId = ?;";

$statement= $db->prepare($query);

$statement->execute(array($userinput));
MrVimes
  • 3,212
  • 10
  • 39
  • 57
  • Thanks MrVimes. However for my case, I somehow have to use [dbName].[dbo].[User] ... It just doesn't work for this only without [dbName] . If you can guess the reason, please let me know. Other queries work fine. Anyways, after a few wonderful hours of debugging, the fight is over... :) That yahooId comes from a session, so I guess, no security holes. I am using prepared statements for some other functions where parameters are complex, but to be honest this one wasn't working even when I was giving table name as a bindParam... – Nikhil Khullar Jan 25 '14 at 10:46