Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


Comma needed in $mysqli->real_escape_string
New on LowEndTalk? Please Register and read our Community Rules.

All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.

Comma needed in $mysqli->real_escape_string

dnwkdnwk Member

If anyone have experiences handling character escapee in PHP could suggest what to do.

In the name field of my PHP app, it was stored as(string) Lastname , Firstname. However, when I put in the exact format lastname,firstname in a search form, the comma was filtered out by real_escape_string. And apparently, LIKE %% in mysql does not find anything without that comma.

So, what should I do without comprise the security by remove real escape?

Comments

  • dnwkdnwk Member
    edited July 2013

    too late to switch to PDO. I am not going to rewrite everything. Stick with MYSQLI at the moment.

  • perennateperennate Member, Host Rep

    Maybe you need to set the charset? Or maybe the input is somehow malformed? ASCII comma shouldn't be removed by real escape string.

  • krokro Member

    Pdo over 7 years old. No excuses. If you took 10mins to understand the changes involved it would take no more than a day to convert a vps control panel.

    Its nicer to work with too, bind away ^_^

  • natestammnatestamm Member
    edited July 2013

    I believe what he is saying is that the data is stored comma separated. His search form escapes the string "blah,doh" for searching and he is using simplified SQL statements using LIKE %% to find a result set. This is not a PDO versus the world starring the Avengers issue...Let me rephrase, it doesn't HAVE to be. The data storage is convoluted to begin with. I wouldn't recommend focusing in on that type of changeover if it's kept the same. This will take some flavor of stored procedure or regex if you want a powerful search.




    At least do more to format the search string. Your wild cards are only for pre and post parameter. Keeping in mind what I have below you can for example split your parameters in your application and then in SQL go for an IN () based on the comma or join result sets and so on and so forth, At the end of the day this is more about how powerful do you want your search to be. Making it about the escaped comma is oversimplifying the goal, and since he's not getting the results he wants any ways I'm just saying broaden the end result, no pun.




    @dnwk You will need to get comfortable using stored procedures and/or regular expressions. There are literally 3-4 different ways to do this that I can think off the top of my head that may involve changing some application logic, changing how data is stored, all the way to doing away with LIKE %% and forcing you to decide whether to put the load on your application code or your RDMS. I would go with the latter and do some fancy SQL coding so that you can find your data properly and learn some thing at the same time. LIKE isn't going to cut it. And I'm too busy with work to write up an example. Sorry!




    Here: Keep in mind even the OP to the Answer is crying about doing this in your scripting language instead. I can answer the why though, Because coding SQL really is fun! http://stackoverflow.com/questions/11835155/mysql-split-comma-seperated-string-into-temp-table

  • dnwkdnwk Member

    @natestamm It looks like it is comma separated, but it is not. For example, this is the string in the database "Black, Jack" . User enter "Black, Jack" the exact same string as what in the database and want to locate that record. I have an auto compete in the form, so that users are likely to choose the exact same string.

  • dnwkdnwk Member

    @kro you always thinking it is 10 minutes to change to PDO. I have no problem spending 10 minutes to convert to PDO for this one query. But there are thousands of queries in the whole system. And if I need to change them all, it is about 1 year for me to do that.

  • @dnwk Yes that's what I was trying to say, and let these other guys know. Lots of ways to handle that type problem.

Sign In or Register to comment.