Jump to content

Database Insertion


DoxramosPS

Recommended Posts

I think this would go in the primary mangos question not ytdb since it has to do with realmd not mangos database.

 <?php
$con = mysql_connect("localhost","root","g278535814");
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }

mysql_select_db("realmd", $con);

$sql="INSERT INTO account (username, sha_pass_hash, email, expansion)
VALUES
('$_POST[username]','$_POST[sha_pass_hash]','$_POST[email]','2')";

if (!mysql_query($sql,$con))
 {
 die('Error: ' . mysql_error());
 }
echo "1 record added";

mysql_close($con);
?>

Is what I currently have for my registration page that I'm working on (which will be cleaned into real_escape_string after I have this sorted out.

How do I get the php/sql command to insert into sha_pass_hash with encrypted data rather then raw text? Thank you in advance.

Link to comment
Share on other sites

darn Zevran posted seconds before me. however make sure you convert the username and password to upper case before hashing

$sha_pass_hash = sha1(strtoupper($_POST['username'] . ":" . $_POST['password']));

See ./game/AccountMgr.cpp std::string AccountMgr::CalculateShaPassHash for reference

Edit: Also, php returns the hash in lowercase, you can convert it to upper case as well so it's consistent with how mangos stores the hashes in the database when it creates them (the lower case could be an issue if your account table is *not* using a case-insensitive character set, but i believe by default all tables are case-insensitive)

Link to comment
Share on other sites

you can change your line

$sql="INSERT INTO account (username, sha_pass_hash, email, expansion)
VALUES
('$_POST[username]','$_POST[sha_pass_hash]','$_POST[email]','2')";

to

$sql="INSERT INTO account (username, sha_pass_hash, email, expansion)
VALUES
('$_POST[username]',sha1(strtoupper($_POST['username'] . ":" . $_POST['password'])),'$_POST[email]','2')";

of course make sure to fix your quoting and add some string escaping before actually implementing any of this ;)

Edit: and no worries about the questions, most of the people in this community began in the exact same spot with limited to no knowledge. The whole reason the community exists is to collaborate and learn together

Link to comment
Share on other sites

Alright, well I changed my registration and now I get this when I attempt to register

Parse error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) in C:\\Mangos Wrath\\Server\\htdocs\\register.php on line 106

Link to comment
Share on other sites

('$_POST[username]','$_POST[sha_pass_hash]','$_POST[email]','2')";

with that your are single/literal quoting your variables, and you are not quoting your array index names, it will work without but will throw a warning and cause issues if you have constants with the same name, I would do it as

($_POST['username'],$_POST['sha_pass_hash'],$_POST['email'],'2');

Note: that is just a snippit of that code, your entire query ($sql) variable needs a little adjusting

what does your code look like that is causing the error?

Link to comment
Share on other sites

<form id="form1" name="form1" method="post" action="">



   <?php
$con = mysql_connect("localhost","root","g278535814");
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }

mysql_select_db("realmd", $con);

$sql="INSERT INTO account (username, sha_pass_hash, email, expansion)
VALUES
('$_POST[username]',sha1(strtoupper($_POST['username'] . ":" . $_POST['password'])),'$_POST[email]','2')";

if (!mysql_query($sql,$con))
 {
 die('Error: ' . mysql_error());
 }
echo "Account Created 
Remember to set your realmlist to 127.0.0.1 to connect and play!";

mysql_close($con);
?>

Is what I'm currently using

I'm going to change the single quoting on the variables like you recommended, I just thought that would cause issues.

I just changed my SQL String to

$sql="INSERT INTO account (username, sha_pass_hash, email, expansion)
VALUES
($_POST['username'],sha1(strtoupper($_POST['username'] . ":" . $_POST['password'])),$_POST['email'],'2')";

and I still have the error inside of Dreamweaver.

Link to comment
Share on other sites

Oh i'm sorry, you are correct you do still need quoting around the values, i mistakenly left them out.

When i read your query for some reason i was reading it as if you were using the sprintf function. but yes you should do

$sql="INSERT INTO account (username, sha_pass_hash, email, expansion)
VALUES
('".$_POST['username']."','".sha1(strtoupper($_POST['username'] . ":" . $_POST['password']))."','".$_POST['email']."','2')";

Link to comment
Share on other sites

My next endeavor is to get pvp rankings and such working and then I'll release until I learn enough php to start turning it into a CMS.

Okay, so I'm working on putting the real_escape_string into place after doing some reading

$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
OR die(mysql_error());

$query = "INSERT INTO users VALUES
(
   '" . mysql_real_escape_string($_POST["username"]) . "',
   '" . mysql_real_escape_string($_POST["sha_pass_hash"]) . "',
   '" . mysql_real_escape_string($_POST["email"]) . "',
   '2'
)";

mysql_query($query);

Would have been the old syntax to use I believe

However I don't see how I would convert my current code to it. Everything looks like it would come up incorrectly

<?php
$con = mysql_connect("localhost","root","g278535814");
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }

mysql_select_db("realmd", $con);

$sql="INSERT INTO account (username, sha_pass_hash, email, expansion)
VALUES
('".$_POST['username']."','".sha1(strtoupper($_POST['username'] . ":" . $_POST['sha_pass_hash']))."','".$_POST['email']."','2')";

if (!mysql_query($sql,$con))
 {
 die('Error: ' . mysql_error());
 }
echo "Account Created 
Remember to set your realmlist to 127.0.0.1 to connect and play!";

mysql_close($con);
?>

I don't see how I would implement real escape string at all :(

I have tried a few times and Dreamweaver lets me know that hey; you're entire code is screwed all up.

Link to comment
Share on other sites

$sql="INSERT INTO account (username, sha_pass_hash, email, expansion)
VALUES
('".mysql_real_escape_string($_POST['username'])."','".sha1(strtoupper($_POST['username'] . ":" . $_POST['sha_pass_hash']))."','".mysql_real_escape_string($_POST['email'])."','2')";

the code sha1(strtoupper($_POST['username'] . ":" . $_POST['sha_pass_hash'])) and '2' don't need to be escaped, since the 2 is not user input and static, and the hash won't include any breaking characters also note that your POST variable $_POST['sha_pass_hash'] should be just the plain clear text hash since the sha1() function call is performing the hash

Edit: You can escape the hash if you wish, just make sure you do not escape the data your passing to the sha1 function as that would break your hash

Here is a snippit of some code i use so you have something to compare to (i'll provide 2 examples one with sprintf and one with the format your using)

With sprintf (i like using single quotes)

$query = sprintf('INSERT INTO account(username,sha_pass_hash,email,expansion,joindate) VALUES(\\'%s\\',\\'%s\\',\\'%s\\',\\'%s\\',NOW());',
      mysql_real_escape_string(strtoupper($_POST['name'])),
      strtoupper(sha1(strtoupper($_POST['name'].":".$_POST['password']))),
      mysql_real_escape_string(strtoupper($_POST['email'])),
      intval($_POST['exp']));

Matching your format (a few tweaks so the extra white space isn't added to the query)

$query = "INSERT INTO account(username,sha_pass_hash,email,expansion,joindate) VALUES(" .
      "'" . mysql_real_escape_string(strtoupper($_POST['name'])) . "'," .
      "'" . strtoupper(sha1(strtoupper($_POST['name'].":".$_POST['password']))) . "'," .
      "'" . mysql_real_escape_string(strtoupper($_POST['email'])) . "'," .
      "'" . intval($_POST['exp']) . "'," .
      "NOW()" .
      ");";

Link to comment
Share on other sites

When you create an account using the console commands in mangos it stores everything in uppercase. My use of strtoupper is mainly to be consistent, but also in case that the table were to be case sensitive the lowercase strings could cause issues since mangos pulls compares the user input straight in the DB for logging in.

the case sensitive issue means

with case-insensitive

antiroot==ANTIROOT is true

with case-sensitive

antiroot==ANTIROOT is false

why this matters is that when mangos does something like 'SELECT * FROM account WHERE username="ANTIROOT"' that query would fail if my username was entered as 'antiroot' (again this only matters on a case-sensitive table)

Link to comment
Share on other sites

in my examples i used intval($_POST['exp']), you can name it anything you want, so that the user could choose which expansion they wanted, I seem to remember having a case where one of my users had their client updated to the supported version yet they did not have all of the wotlk expansion content. one thing you could do is setup your code so that it will use a POST variable for the expansion if specified or default to something

$expansion = (isset($_POST['expansion']) && $_POST['expansion'] >= 0 && $_POST['expansion'] <= 2 ? $_POST['expansion'] : 2);

then in your query you would just use the $expansion variable (or just use that whole line in your query and not use the extra variable declaration)

but if you just want to force expansion 2 no matter what then using what you had before would be fine

Edit: the above does 3 checks, one to see if the variable POST['expansion'] exists, one to see if it's greater than or equal to 0, and the third check makes sure the value is less than or equal to 2. by using the && operators if any of the 3 checks fail the default value of 2 is used, if all three checks pass than the user specified POST['expansion'] is used. If you are un familiar with that syntax, it is called a ternary operator basically it's the equivalent to an if statement that returns 2 values depending on the conditions

Another Edit: slightly off topic from your questions, but in case you have not done so yet make sure you check if the username is available before attempting to create the new account. You don't want to end up with accounts using the same name

Link to comment
Share on other sites

I will probably have it force 2 until I have a better understanding of how it works. As far as multiple usernames the server returns that the name exists. After i finish the page out completely i plan on sharing for user feedback and eventually taking it cms. Should probably get it on github lol

Link to comment
Share on other sites

Total I have

  <?php
$con = mysql_connect("localhost","root","g278535814");
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }

mysql_select_db("realmd", $con);

$query = "INSERT INTO account(username,sha_pass_hash,email,expansion,joindate) VALUES(" .
      "'" . mysql_real_escape_string(strtoupper($_POST['username'])) . "'," .
      "'" . strtoupper(sha1(strtoupper($_POST['username'].":".$_POST['sha_pass_hash']))) . "'," .
      "'" . mysql_real_escape_string(strtoupper($_POST['email'])) . "'," .
      "'" . intval(2) . "'," .
      ",NOW()" .
      ");";

if (!mysql_query($query,$con))
 {
 die('Error: ' . mysql_error());
 }
echo "Account Created 
Remember to set your realmlist to 127.0.0.1 to connect and play!";

mysql_close($con);
?>

I get

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOW())' at line 1

I thought removing the NOW and the joindate would fix it, but that ended up opening a new can of worms lol.

Link to comment
Share on other sites

looks like there is an extra comma character

either remove the one after intval(2)

"'" . intval(2) . "'," .

or the one before NOW()

",NOW()" .

one thing i do when debugging is echo the query so i can see what is being passed to mysql after all the php variables are filled and everything is parsed

Edit: in fact that extra comma looks like it was my fault because i have that in my example (which i'll be editing so it will be correct)

one more edit: You don't actually need intval(2), since your 2 is static and an int to begin with

Link to comment
Share on other sites

With the 2 being static would it just be

 "'" (2) . "'," .

?

EDIT:

I suppose I could have tested it first lol. I got rid of the comma in front of NOW and it's good to go =D Going to make some more changes and find a place to put it on here for people to see if they like it or not. Keep in mind; it's the first reg page I made so don't hate :P.

Link to comment
Share on other sites

"'2'," .

would suffice, however the single quotes aren't required either so just simply

"2," .

would work as well, my personal preference is to quote any value that is not a function/routine call (for example NOW() )

whatever method you prefer to generate the query, your end goal is to have something similar to the following for what your query should be when passed to mysql or echoed to the screen

INSERT INTO account(username,sha_pass_hash,email,expansion,joindate) VALUES
('ANTIROOT','B4831A22B8ABFE73939B9E5C6BEF9F4D2F299C59','[email protected]','2',NOW());

Link to comment
Share on other sites

Being so new to php and all I'm still full of questions haha

If I was to add

elseif (empty($email_address)) {
header( "Location: $error_page_email" );
}

And create a

error_page_email.php inside my root and then set the variable to

$error_page = "error_page_email.php";

In the top; that should send them to a php page that I create called error_page_email.php correct?

Link to comment
Share on other sites

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue. Privacy Policy Terms of Use