Clever Geek Handbook

Mysql "magic" catches entire column for select statement

Is there any way I can do as such

select * from attributes where product_id = 500

will return

id  name     description
 1  wheel    round and black
 2  horn     makes loud noise
 3  window   solid object you can see through

and request

select * from attributes where product_id = 234

will return the same results as any query on this table.

Now, obviously, I could just remove the where clause and go to my day. But it has to do with editing code that I really don't want to change, so I'm trying to fix it at the database level.

So, is there a "magic" way to ignore what's in the where clause and return whatever I want with a view or whatever?

0


source to share


5 answers


Even if it was possible, I doubt it would work. Both of these WHERE clauses expect one thing to be returned, so the code will probably just use the first row it returns, rather than all of them.

This will also give the database behavior that will make future developers pull their hair out trying to figure it out.



Do it right and fix the code.

+3


source


or you can pass "product_id" instead of an integer if there is no code validation for that ... so the query would look like this:

select * from attributes where product_id = product_id;



this will give you every row in the table.

+2


source


If you are unable to edit the request, perhaps you can add it to it? You can stick with

OR 1=1

at the end.

+2


source


You might be able to use the result set metadata to get what you want, but the result set will not contain field descriptions. The specific API for getting result set metadata from a prepared query is programming language dependent and you did not specify which language you are using.

You can query INFORMATION_SCHEMA for the product table.

SELECT ordinal_position, column_name, column_comment
FROM INFORMATION_SCHEMA.columns
WHERE table_name = 'products' AND schema_name = 'mydatabase';

You can refactor the database into an Entity-Attribute-Value project , but this is a much more ambitious change than a code fix.

Or, you can opt out of SQL databases altogether and use a semantic data store like RDF , which allows you to query the metadata of an object the same way you query data.

+1


source


As far as this idea seems to me, I'm always interested in crazy ways to do things. I think the best solution I could come up with is to use a view that uses a product table to get all the products and then an attribute table to get the attributes, so every possible product is counted and everyone will get the same result.

0


source






More articles:


All Articles