Normal Query Syntax Not Working, '' For Numeric

[LLLLLLL]

On one customer website, the customer cannot insert any rows if a field is left blank and the column is a number. In short...

''

... is not being accepted for numeric or decimal columns.

 

I've never had this on any other customer site, and it's pretty standard to use '' for column values in by MySQL (and even MS-SQL).

 

What would be preventing this? I assume it's a MySQL setting somehow? For what it's worth, this customer is on a Windows server, much to my chagrin.

[Pikachu2000]

What is the output when you echo the query string?

[LLLLLLL]

It is as you would expect...

insert into some_table

( col_a, col_b, col_c )

values

( '1', 'this might be a string column', '123.00' )

 

And the error is clear:

Incorrect decimal value: '' for column col_a

[fenway]

Sounds like you have STRICT mode enabled.

[LLLLLLL]

Right... but my question is how to disable it? Something in php.ini? (This is a customer's server and a Windows box.)

[fenway]

Look for sql_mode in the my.cnf file.

 

Of course, you shouldn't disable it -- why not enter 0, or NULL? A string isn't a valid number.

Edited November 24, 2012 by fenway

[LLLLLLL]

Because this is how the application works. It's quite common to build queries out this way, even in MS-SQL.

[Barand]

It is never common to put numeric values in quotes, just wrong

[LLLLLLL]

It is quite common to do this, and not wrong at all. If the application is building queries and doesn't know the column types, this is how to do it.

[Barand]

One of the problems with MySQL is that it is too lenient of bad practices. Numbers should not be quoted in SQL

[Pikachu2000]

Something can be common and still be wrong.

[LLLLLLL]

MS-SQL allows this, too.

 

I think it depends on the language; PHP is weakly-typed so its database pretty much needs to allow this type of syntax. If you're in C# with an MS-SQL back-end, then you'll know the data type and quoting isn't necessary.

 

Again, without writing all sorts of code and/or performing extra reads, quoted values is the only way to go. There's no easy solution without it.

[Pikachu2000]

PHP's typing has nothing to do with whether or not you quote a value in the query string.

[LLLLLLL]

Um, yes it does. How do I know what the type is?

[Pikachu2000]

You designed a database not knowing what field will hold what type of data?

[LLLLLLL]

I'm saying, how can the code dynamically generate the query without quotes? (Without doing extra reads or writing lots of extra code.)

 

You can't. You get data from a form, and you don't know anything except that you have data. The code doesn't know that some textbox is numeric only, or that some select box values are ints.

[Pikachu2000]

That's the purpose of validation and sanitation, not arbitrarily throwing all the values in quotes.

[LLLLLLL]

I see you're trying to chest-thump here, so go for it... how do you know which columns to NOT throw in quotes, then?

[Pikachu2000]

Chest-thump? Really? Grow up.

 

You leave out quotes for values that are going into a numeric data type field in the database, after validating the data/casting it as the correct type.

[LLLLLLL]

You have yet to answer my question. How does the app know the data type?

 

You either write a bunch of extra code or you do extra DB reads. Or you use quotes, like all DB engines allow, and everything works as expected.

Edited November 25, 2012 by timneu22

[Pikachu2000]

Because you've validated/sanitized/cast it as the correct data type to make it safely usable in a query.

[LLLLLLL]

Who's on first??

 

But how does the app KNOW WHAT THE CORRECT DATA TYPE IS? Come on, how many times can you not answer the question?

 

You either write lots of extra code or do lots of extra DB reads. Or you use quotes like all DB engines allow.

[Pikachu2000]

No, you don't "write lots of extra code". Where do you even get that idea? If you wrote the code, you designed the database, you know what data comes from where, then you know what data type it is, and where it should be going. How can you NOT know what data type should be going in which field in the database? If you don't understand this, it certainly isn't because I haven't answered the question.

[LLLLLLL]

You clearly have never written an application that uses abstraction and base classes. This is basic stuff.

 

The base class doesn't know about every field for every column. It knows "I have these POST values" and "here's my table". That's all it knows. The base class generates a query from that. It's unaware of every column type.

 

So the answer is .... quoted columns in the query string. A standard feature in DB engines.

 

I'm got the answer to my specific question a while ago. Our conversation, while enjoyable, didn't help anyone. I'm no longer following this thread.

[Jessica]

Apparently OP isn't aware of prepared statements.