SQLSTATE: 42000

Question

I've been trying to insert items into a SQL Server database using PHP but I keep getting back this error:

SQLSTATE: 42000 code: 102 message: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near '<'.

Here's the sql statement:

$query = "INSERT INTO [competitionTrade]
                       ([fullName]
                       ,[date]
                       ,[limitOrder]
                       ,[quantity]
                       ,[tickerSymbol]
                       ,[limitPrice]
                       ,[contingentOrder]
                       ,[contingentQuantity]
                       ,[contingentLimitPrice])
                 VALUES
                       (<$fullName, varchar(50),>
                       ,<$date, datetime,>
                       ,<$limitOrder, varchar(50),>
                       ,<$quantity, int,>
                       ,<$ticSymbol, varchar(50),>
                       ,<$limitPrice, float,>
                       ,<$contingentOrder, varchar(50),>
                       ,<$contingentQuantity, int,>
                       ,<$contingentLimitPrice, float,>)";

I'm not too sure where the syntax error is in this statement.

Why are you setting the field type in the INSERT, removed this and just insert the value — ChrisBint, Jun 12 '15 at 20:42
use PDO. search for best practices to building a safe query on PHP — Elzo Valugi, Jun 12 '15 at 20:47

score 2 · Accepted Answer · answered Jun 12 '15 at 20:44

Your SQL is totally broken:

             VALUES
                   (<$fullName, varchar(50),>
                   ,<$date, datetime,>

That is NOT how you write an insert:

INSERT INTO table (field1, field2, ...., fieldN)
VALUES (value1, value2, ...., valueN)

You specify field types when you CREATE the table. And as written, if it were working, you are probably vulnerable to sql injection attacks.l

score 1 · Answer 2 · edited May 23 '17 at 11:58

< is not valid syntax. It should just be ... VALUES ($fullName, $date, $limitOrder, $quantity, $ticSymbol, $limitPrice, $contingentOrder, $contingentQuantity, $contingentLimitPrice)";

Insert statements does not require you to put field types, they just require the data you wish to insert.

I recommend reading up on how to bind variables to query to prevent SQL injection. Have a look here for more info.

score 1 · Answer 3 · answered Jun 12 '15 at 20:45

You don't need to set the field type on an INSERT statement. Try this:

$query = "INSERT INTO [competitionTrade]
                       ([fullName]
                       ,[date]
                       ,[limitOrder]
                       ,[quantity]
                       ,[tickerSymbol]
                       ,[limitPrice]
                       ,[contingentOrder]
                       ,[contingentQuantity]
                       ,[contingentLimitPrice])
                 VALUES
                       ($fullName,
                       $date,
                       $limitOrder,
                       $quantity,
                       $ticSymbol,
                       $limitPrice,
                       $contingentOrder,
                       $contingentQuantity,
                       $contingentLimitPrice";

asumming that the variables are safe, still not the best way to add variables. Use PDO — Elzo Valugi, Jun 12 '15 at 20:46

SQLSTATE: 42000

3 Answers3