A column with implanted PHP functionality

Question

Lets say I have this PHP function:

function strlow($string){
      strtolower(trim($string));
}

Now lets say I have a table with 2 columns [id - title] and I want to make all titles that are going to be inserted into the table in lowercase, The usual way is

$title = strlow($title);
$query = "INSERT INTO table (title) VALUES ($title);

Is there for example a way to implant in the column itself in the database the function, So instead of doing the strlow() by the PHP, the Database does it?

If yes, I wish for an example built on mine.

`$query = "INSERT INTO table (title) VALUES TRIM(LCASE(($title)));`? — ceejayoz, Jul 14 '17 at 17:41
FYI, your code is **wide open** to SQL injection... never concatenate data directly into a query. Use parameterized queries. — Brad, Jul 14 '17 at 17:42
`$query = "INSERT INTO table (title) VALUES (LOWER($title))";` — Jigar Shah, Jul 14 '17 at 17:42
@ceejayoz Well, That good, But what if i wanted to use for example `htmlentities` or `preg_replace` — Calibur Victorious, Jul 14 '17 at 17:42
@CaliburVictorious There's a preg_replace equivalent in MySQL. Don't believe there's a htmlentities one. Why do you want it in the MySQL side? — ceejayoz, Jul 14 '17 at 17:44
@ceejayoz I thought it would be very useful and efficient if MySQL can handle such things by itself, Instead of PHP. — Calibur Victorious, Jul 14 '17 at 17:45
MySQL probably isn't any more efficient at these things than PHP is. — ceejayoz, Jul 14 '17 at 17:47
@CaliburVictorious: MySQL can do some things that PHP can do, and vice-versa. But they are ultimately different tools which do different things. The specific example you gave was answered, but when you reply with something like "but what about [some other edge case]?" then it makes the question much more open-ended and unanswerable. What exactly are you asking? Can MySQL do *everything* PHP can do? Certainly not. — David, Jul 14 '17 at 17:47
As per my answer I would definitely recommend keeping this in the PHP application layer. If you have some logic nested in MySQL and some in PHP it will just give you headaches in future — Peter Featherstone, Jul 14 '17 at 17:48
*"But what if i wanted to use for example htmlentities or preg_replace"* - Is this in regards to avoiding sql injection? Your question is getting unclear at this point. — Funk Forty Niner, Jul 14 '17 at 17:49
@David I'm pretty sure contained both parts, One about example, And another about the [edge cases] — Calibur Victorious, Jul 14 '17 at 17:49
@Fred-ii- My question was about if MYSQL has a cooperative functionality with PHP that instead of typing your code in pages, You type it in the database itself, And it does all the job, Same as including functions page. — Calibur Victorious, Jul 14 '17 at 17:52
[This comment that you posted earlier](https://stackoverflow.com/questions/45108827/a-column-with-implanted-php-functionality#comment77189059_45108827) was based on what I wrote. — Funk Forty Niner, Jul 14 '17 at 17:53
@Fred-ii- Well, It is still the same, Like if the database was actually very cooperative with PHP, So i type in a column(php functions) inside the database, to do htmlentities or preg_replace. But it seems that it is not possible, So the question is answered. — Calibur Victorious, Jul 14 '17 at 17:55

Peter Featherstone · Accepted Answer · 2017-07-14T17:50:30.157

2

You could update your query to handle this if you really wanted (but I would still rather do this in the application layer) using the MySQL TRIM and LOWER commands:

INSERT INTO table (title) VALUES (TRIM(LOWER(($title)))

The reason I say I would rather do this in the application layer is that if you decide to switch database systems in future, you need to remember to port over all your database formatting rules such as these at that time too which although doesn't seem too bad now, trust me, in the future, you will forget.

In addition to this, if you ever want to add further logic to what you are putting in to the database you will likely find your options more limited in MySQL than you will in your application layer.

Also, please for my sanity look up how to use parametrized queries because you are wide open to SQL injection attacks at the moment. There is a great post here that covers this.

edited Jul 14 '17 at 17:50

answered Jul 14 '17 at 17:43

Peter Featherstone

7,835
4
32
64

*"because you are wide open to SQL injection attacks at the moment"* - Nothing suggests user input, not with what they posted. Least, they didn't really say that `$title` is coming from user input. – Funk Forty Niner Jul 14 '17 at 17:51
1

@Fred-ii- Ok then, *semi wide open*. If not using parametrized statements from the start it is easy to get into a situation where you start accepting user input and forget to update this part of the code. I've seen it before, I don't want to see it again and I'd rather be diligent from the start. The `$title` could be coming from a user constructed comment or page or post so I'd rather be safe than sorry, mention it and leave it with the OP to decide on the best course of action for their data. In either case I would never not parametrize any piece of data going into my database. – Peter Featherstone Jul 14 '17 at 17:53
@Fred-ii- It doesn't matter where the data comes from. It can contain characters which makes the delineation between data and command structure ambiguous. Data must **always** be escaped for use in another context. – Brad Jul 14 '17 at 17:53
@Brad if there's no user intervention, then why the additional functions/resources? – Funk Forty Niner Jul 14 '17 at 17:54
@Fred-ii- I would rather a small use of additional functions/resources than risk the safety and integrity of all my sites data... – Peter Featherstone Jul 14 '17 at 17:56
@PeterFeatherstone I'm not saying there's anything wrong with it. But why use up additional resources if there is no user input, as I stated to Brad(?). I can't see an injection happening otherwise. – Funk Forty Niner Jul 14 '17 at 17:57
@Fred-ii- I guess it's the old argument of `YAGNI` against `Future Proofing`. Maybe it's my personal opinion but I would sleep better at night knowing that all my variables are parametrized. For me, I just feel it guards against the future case of someone changing something that allows the acceptance of user data and at the same time not updating the database calls. If the parametrization is already in place then the dev would be automatically lead down this path. – Peter Featherstone Jul 14 '17 at 17:59
@Fred-ii- Suppose you actually want a title with a quote in it. What now? Suddenly all your code is **broken** and you and your users will have a bad day. Plus, to echo what Peter is saying... code that calls whatever function is doing this inserting shouldn't have to know or care about the backend data implementation. By not doing this correctly you're introducing side effects. If this is the attitude you've taken in your code, no doubt pretty much everything you've built this way is broken and it's only a matter of time before it bites you. It happens to people every day. – Brad Jul 14 '17 at 18:13
@Brad get over it, I did; a long time ago. – Funk Forty Niner Jul 14 '17 at 18:18
@Brad please don't get over it and please keep advocating best coding practices as I will be. I appreciate both of your comments, time for a beer down the pub now I think... – Peter Featherstone Jul 14 '17 at 18:25
@Fred-ii- Get over it? Folks who code like you do are the reason everything we all use is broken all the time. What's there to get over... you're introducing bugs. – Brad Jul 14 '17 at 18:32

score 0 · Answer 2 · answered Jul 14 '17 at 17:45

0

$query = "INSERT INTO table (title) VALUES (LCASE($title));

answered Jul 14 '17 at 17:45

Kevin P

601
3
9

A column with implanted PHP functionality

2 Answers2