[SOLVED] How to enter height in to MySQL data base

This might be stupid but I tried to add height in to the data base in this format 5’3" but apostrophe and the double quote is a special character in MySQL and then I have to display it with the same formatting in the site. I’m not a pro in PH still got lot to learn so can some one show me how to archive this and if any one want I’m listing the function php code.

P.S: I know about the security issues I will rewrite this with better SQL security.

<?php
/**
 * Created by PhpStorm.
 * User: SiNUX
 * Date: 9/13/14
 * Time: 9:44 PM
 */
include_once("hostcon.php");

$name = $_REQUEST["name"];
$niName = $_REQUEST["nicname"];
$age = $_REQUEST["age"];
$bust = $_REQUEST["bust"];
$size = $_REQUEST["size"];
$height = $_REQUEST["height"];
$hair = $_REQUEST["hair"];
$eyes = $_REQUEST["eyes"];
$nAtio = $_REQUEST["natio"];
$dEscrip = $_REQUEST["desc"];
$stdService = $_REQUEST["stdService"];
$othService = $_REQUEST["othService"];

//Adds data from the options array in to one line
$std = implode(", ",$stdService);
$oth = implode(", ",$othService);

//inserting data in to the data base

$newModel = "INSERT INTO `modeldetails`(`name`, `nicName`, `age`, `bust`, `height`, `hair`, `eyes`, `natio`, `descrip`, `stdService`, `otService`) VALUES('".$name."','".$niName."','".$age."','".$bust."','".$height."','".$hair."','".$eyes."','".$nAtio."','".$dEscrip."','".$std."','".$oth."')";

$fireQuary = mysql_query($newModel);
//echo mysql_error();

if($fireQuary){
    header("location:newmodel.php?msg=added");
    exit();
}else{
    header("location:newmodel.php?msg=notadded");
    exit();
}

I think if you switch to PDO, you can get around the quotes problem when using bindParam as the strings are escaped prior to storing.

yeah I thought of that m8 but I really can’t get my head around PDO thinking of doing this in mysqli but first like to get this working.

And thank you for the reply.


$a = $_POST['height'];
echo $a;

$q = "insert into temptable(height) values(:ht)";
$r = $dbc->prepare($q);
$r->bindParam(':ht', $a);
$r->execute();

When you browse your table, it will have escaped the quotes in the string automatically, so you’ll have to deal with that when you display them. Or you could convert to inches prior to writing, then have a function that splits it back again to display. As you said you need to deal with the security issues anyway, PDO will help that as well.

Thank you yes, security I will deal with it but PDO I’m lost mysqli I think I can do it will try your solution to see if I can get it to working to insert height.

@sand12

That’s a SitePoint article that covers converting from the old mysql_* extension over to PDO

1 Like

To specifically answer your question, you need to escape the quotes in order to insert them. It’s advised that you use the escaping function native to the database API you’re using. In this case, it’s the mysql_real_escape_string() function. Applying that to a string like 5'3" will return the string 5\'3\", which can then be safely inserted into your database.

Why not just do it correctly the first time? There is little point writing insecure code first, only to have to rewrite it later with security in mind. Even if this is a self-learning excessive, I can’t see the value in teaching yourself how do to it incorrectly first.

Just to highlight this point, even though the previous posters have already pointed this out: The very same security measures you are avoiding would also have fixed the problem you are facing. Regardless of the API you chose, whether you were to escape the input, as was done in the now deprecated MySQL API extension, or by prepared statements, as is done in PDO and MySQLi, both methods would prepare the input value in such a way that it wouldn’t mess up the SQL query.

1 Like

No need to push him to switch to PDO, if he does the escaping wisely he will stay secure. This means height is not the only variable that needs to be escaped - in this case virtually every variable must be escaped because it comes from unpredictable source ($_REQUEST). If you don’t do it then your site is vulnerable to sql injection attacks!

Having said that, giving up the deprecated mysql extension is a good idea - switching to mysqli is very easy. Therefore, mysqli_real_escape_string() is the function you want to use for every variable in your query.

BTW, in my opinion it would make more sense to store height in a numeric column and not as a string because height is really a numeric value. If you store it as string you can’t easily do numeric operations like comparing heights, ordering by height, converting to a different unit, etc. I would store height in a TINYINT, DECIMAL or FLOAT column type as a number of inches and then format it for display the way I want it.

2 Likes

That is a big if. The problems with escaping isn’t so much that escaping itself is insecure, but rather that the manual variable injection is an added risk factor. Even experienced developers, who know better, are at risk of accidentally injecting an insecure value. Amateurs even more so.

You’re in the middle of updating a legacy script on your system, assign your post value to a local variable, your next move being to add an escape call to the line… when you get a call from your boss. You come back a few minutes later, now in a hurry to get this done and move on, and inject your variable into the query, not realizing you never escaped it. (This is actually a true story :smiley:)

If you switch to PDO or MySQLi, and use prepared statements, there is really no way to accidentally inject insecure values. They are always place-holders in the actual query, and will always have to pass through the bind calls.

I couldn’t agree with you more on that, by the way. This value should be stored as a number, not in it’s display form.

That is a very good point on getting distracted in some way and not always remembering to do your escaping. We are after all only human, and we can never get 100% of things done correctly!
From what I understand, PHP is really moving towards PDO as a standard, inherent security being one of the main reasons.

So while I understand that it is a bit of a learning curve for newer devs, maybe pushing him a bit as a community is a good thing :smile:

@sand12 the PDO might seem a bit tricky, but if you spend half an hour looking at it, I’m sure it will make sense and will mean you can start applying it to everything you do in PHP from now on (including any database you use).
Good luck.

First of all thank you very much for all the advice secondly please don’t make this a do it this way kinda thread as I told I know the vulnerability of my script what I’m trying to is to finish the job using what I know then dive in to unknown waters like PDO ( but I’m more interested in using mysqli ) any way as leamon_juice said I will try the escape switch and will store it as and numeric value. There’s no need yet to do calculations but you never know.

I will do the changes according to your advices also like to add another I’m fond of using ($_REQUEST) to get my data is it goo thing or bad if it’s bad then what should I use?.

Also any one know good books for mysqli and PDO really need learn this two or one of them.

It doesn’t matter whether you use PDO or mysqli.

What you need to do is to only use query calls where there are no variables in the call whatsoever.

When there are variables in the call you should substitute prepare and bind for the query call. The SQL goes in the prepare statement with either ? or :var names as placeholders and then the variables to substitute go in the bind statement (which is combined with the execute in PDO). No escaping is necessary as the data is kept completely separate from the SQL and also there will be no issues whatever with any characters that are valid for any particular field.

So switching to the more modern code completely does away with the problem you are having with the antiquated way you are currently trying to do it. So not only is updating to the more modern approach the right thing to do after you fix your problem - it is also the best way TO fix your problem.

I do agree with you sir, thank you I just want to finish this current one to tell you guys the truth all over my country they teach the old MySQL code and it’s data passing methods that’s why I’m using that I learned it that way. I didn’t know about MySQLi or PDO till recent when I stumbled on to a post on the internet now I’m trying to learn this by my self to make my self better.

To that I need finish off this job so I can invest my time leaning the PDO or MySQLi.

You need to convert all the mysql calls to one of the other two IMMEDIATELY anyway. The mysql interface was flagged for removal many years ago and the time when it will no longer be supported at all is getting very close. Creating still more code that you will need to completely rewrite almost straight away is rather pointless.

The next version of PHP will not have mysql calls.

yes sir I know, I just want to get this client off my back after I give him the site I get time to study PDO or MySQLi then I’m gonna redo the processing part and upgrade it and well he’ll be happy to receive a free upgrade :smile:

I know humans make mistakes and I understand some people are likely to make fewer of them if they bind parameters instead of escaping values but for me personally there is no much difference. Even if I use a database extension that allows me to use prepared statements I have to remember to bind values. If I don’t use prepared statements then I have to escape values. In either case I have to remember to do something with those values because even PDO will allow me to inject insecure stuff by using simple string concatenation. Is there some mental switch in people’s minds that when they use PDO they remember to take necessary steps to ensure security (=bind values) while with mysql(i) they tend to forget to take those steps (=escape values)? I understand people are different so this may be true for them but for me there is no difference and the amount of work in either case is similar.

For me it’s a matter of being aware of having to make every sql call secure and whether I use prepared statements or escape values there’s no fundamental difference since neither mysqli nor PDO will make my sql secure if I don’t consciously write it so.

1 Like

I feel like this thread is going in to a PDO vs MySQLi debate please just help me to solve my mentioned issue I’m thinking of getting these 2 as separate values and entering them as 2 values in to the DB then display them together in my main site.

@tpunt gave you the solution in post #7

That is what I was going to suggest. Another way be to change the ’ and " to something like _ when it is saved to the database and either string replace() or use explode() to change it back again.