Prepare execute a foreach – PHP – Fórum – Programujte.com

Dobrý deň, programujem v PHP nie objektovo. Potreboval by som do scriptov dať execute a prepared dodávam tu script. Okrem toho by ste mi vedeli upraviť int1-8? vraj sa to dá krásne zapísať cez foreach.  

<?php if(isset($_POST['register'])){
   $username = mysqli_real_escape_string($con, $_POST['username']);
   $username = htmlspecialchars( $username );
    $password = mysqli_real_escape_string($con, $_POST['password']);
    $password = htmlspecialchars( $password );
   $email= mysqli_real_escape_string($con, $_POST['email']);
   $email = htmlspecialchars( $email );
  
  if($username == "" || $password == "" || $email == ""){
    echo "Na niečo si zabudol!";
  }elseif(strlen($username) > 20){
    echo "Tvoje meno je veľmi dlhé!";
  }elseif(strlen($email) > 100){
    echo "Tvoj e-mail je veľmi dlhý!";
  }else{
    $register1 = mysqli_query($con,"SELECT `id` FROM `user` WHERE `username`='$username'") or die(mysqli_error());
    $register2 = mysqli_query($con,"SELECT `id` FROM `user` WHERE `email`='$email'") or die(mysqli_error());
    if(mysqli_num_rows($register1) > 0){
      echo "Toto meno je už používané!";
    }elseif(mysqli_num_rows($register2) > 0){
      echo "Tento e-mail je už používaný!";
    }else{
      if($_POST['frakcia'] == "USA"){ $frakcia = 0; }
      elseif($_POST['frakcia'] == "Nemecko"){ $frakcia = 1; 
      }
     if($_POST['frakcia'] == "Slovensko"){ $frakcia = 2; }
     elseif($_POST['frakcia'] == "ZSSR"){ $frakcia = 3; 
      } 
      if($_POST['frakcia'] == "Taliansko"){ $frakcia = 4; }
     elseif($_POST['frakcia'] == "Británia"){ $frakcia = 5; 
      } 
      $code = generate_code();
      $ins1 = mysqli_query($con,"INSERT INTO `stats` (`gold`,`attack`,`defense`,`food`,`income`,`farming`,`battery`,`rank`,`floor`,`quest`,`qok`,`wall`,`cannon`,`ccost`,`cattack`,`wcost`,`wdefense`,`username`,`inv`,`new`,`guild`,`apotion`,`dpotion`) VALUES (100,0,0,200,0,0,100,0,1,1,0,0,0,1000,0,1250,0,'$username',0,0,0,0,0)") or die(mysqli_error($con));
      $ins2 = mysqli_query($con,"INSERT INTO `unit` (`worker`,`farmer`,`rifleman`,`machinegunner`,`tfarmer`,`dog`,`fisherman`,`soldier`,`sergeant`,`gendef`,`schutze`,`soldad`,`maschinengewehr`,`leutant`,`hauptmann`,`hund`,`strelec`,`gulometcik`,`vojak`,`serzant`,`generalobrany`,`pes`,`strelets`,`pistoleti`,`vojnik`,`sershant`,`obscht`,`kuce`,`tiratore`,`artigliere`,`soldato`,`sergente`,`generale`,`enfieldman`,`gunner`) VALUES (0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)") or die (mysqli_error());
      $ins3 = mysqli_query($con,"INSERT INTO `user` (`username`,`password`,`email`,`fraction`,`activated`,`code`) VALUES ('$username', '".sha1($password)."', '$email', '$frakcia', 1, '$code')") or die (mysqli_error());
     $ins4 = mysqli_query($con,"INSERT INTO `weapon` (`springfield`,`m1garand`,`browning`,`pershing`,`hellcat`,`sherman`,`thompson`,`kar98`,`mp40`,`gw43`,`mp44`,`mg42`,`m1a1`,`tiger`,`stugv`,`tiger2`,`howitzer`,`panzerstellung`,`pps43`,`pps41`,`mp38`,`zk383`,`gulometvz24`,`puskavz24`,`pak38`,`flak37`,`grw`,`lt38`,`enfield`,`sten`,`bren`,`bar`,`delisle`,`lewis`,`vickers`,`cromwell`,`comet`,`carcano`,`skoda`,`brixia`,`breda`,`ovp`,`baretta`,`fucile`,`p43`,`carro`,`mosinnagant`,`minometvz40`,`maxim`,`kv2`,`dp27`,`zis3`,`t34`,`is2`,`svt40`) VALUES (0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)") or die (mysqli_error());
     $ins5 = mysqli_query($con,"INSERT INTO `working` (`fyes`,`myes`,`bonus`,`ayes`,`fwork`,`mwork`,`tfwork`,`tyes`,`fishermanwork`,`adyes`,`ad2yes`) VALUES (0,0,0,0,0,0,0,0,0,0,0)") or die (mysqli_error());
      $ins6 = mysqli_query($con,"INSERT INTO `ranking` (`number`) VALUES (0)") or die (mysqli_error());
      $ins7 = mysqli_query($con,"INSERT INTO `timer` (`ftime`,`mtime`,`atime`,`adtime`,`ad2time`) VALUES (0,0,0,0,0)") or die (mysqli_error());
      $ins8 = mysqli_query($con,"INSERT INTO `daily` (`bonus`) VALUES (0)") or die (mysqli_error());
       
      echo "Si úspešne zaregistrovaný!";
      $t = file_get_contents("registracie.txt");
      $today = date("Y-m-d H:i:s");
      $t .= $today." Používateľ ".$username." "."sa zaregistroval!"."\r\n";
      file_put_contents("registracie.txt",$t);
      mail($email,"Nation Wars: ","Vítaj v hre Nation Wars!\r\n\r\nPrihlásovacie meno: ".$username."\r\n");
    }
  }
}}
?>

HTML form: 

					<form action="register.php" method="post">
												<input type="text" name="username" class="inputbox" alt="username" placeholder=Meno style="width:100%" />
												<input type="password" name="password" class="inputbox" size="18" alt="password" placeholder=Heslo style="width:100%" />
												<input id="modlgn_username" type="text" name="email" class="inputbox" alt="email" placeholder=E-mail style="width:100%" />
											</p>
											<script>
var vlajkaa = {
  "USA" : "images/fraction0.png",
  "Nemecko" : "images/fraction1.png",
  "Slovensko" : "images/fraction2.png",
  "ZSSR" : "images/fraction3.png",
   "Taliansko" : "images/fraction4.png",
  "Británia" : "images/fraction5.png"
};
var infoo = {
  "USA" : "Spojenci",
  "Nemecko" : "Osa",
  "Slovensko" : "Osa",
  "ZSSR" : "Spojenci",
  "Taliansko" : "Osa",
  "Británia" : "Spojenci"
};
</script>
Frakcia: 
											<select name="frakcia" onchange="document.getElementById('vlajka').src = vlajkaa[this.value]; document.getElementById('info').innerHTML = infoo[this.value];">
												<option value="USA">USA</option>
												<option value="Nemecko">Nemecko</option> >
												<option value="Slovensko">Slovensko</option>>
												<option value="ZSSR">ZSSR</option>
												<option value="Taliansko">Taliansko</option>
												<option value="Británia">Británia</option>
											</select>
											<br />
Vojnová politika:
											<b>
												<font id="info" style="color: #00cc66">Spojenci</font>
											</b>
											<img src="images/fraction0.png" id="vlajka" style="width: 64px; height: 69px;"/>
											<i></i>
											<br>
												<button type="submit" name="register" class="xmiddle green button round">Registrovať!</button>
                          <hr> Registráciou súhlasíte s <a href="blog/podmienky.php">podmienkami hry!  </a>
												<ul>
													<li>
														<a href="index.php">Prihlásiť sa do účtu</a>
													</li>
												</ul>
											</form>

A co za to? Udelat neco pro cizi lidi je vetsinou odmenovano. Treba 500 kc za kazdou zapocatou hodinu, kdyz se jedna o programovani. Jinak se nema cenu s tim piplat.

   $username = mysqli_real_escape_string($con, $_POST['username']);
   $username = htmlspecialchars( $username );
    $password = mysqli_real_escape_string($con, $_POST['password']);
    $password = htmlspecialchars( $password );
   $email= mysqli_real_escape_string($con, $_POST['email']);
   $email = htmlspecialchars( $email );

// --- Si to uloz do pole, ne?
// --- A escapuj, az je potreba escapovat.

function escapeHtml($str) {return htmlspecialchars($str);}
function escapeSqlValue($str) {return mysqli_real_escape_string($str);} // pokud nepouzivas vic $connection, tak se tam nemusi psat
function escapeUrl($str) {return erlencode($str);} 
function sql($query) {return mysqli_query($query) or die(mysqli_error());}
//function sql($query) {global $con; return mysqli_query($con,$query) or die(mysqli_error());}  // jestli trvas na tom $con

$form      = array();
$form_sql  = array();
$form_html = array();
$filter = array('username','password','email','frakcia');
foreach($filter as $key) {$form[$key] = $_POST[$key];}
$form['code']     = generate_code();
$form['password'] = sha1($form['password']); // sql ma taky SHA1(), ale budiz, pouziji php
foreach($form as $key) {$form_sql[$key]  = escapeSqlValue($form[$key]);}
foreach($form as $key) {$form_html[$key] = escapeHtml($form[$key]);}

// --- na ted priklad, jak se to pouziva
//$ins3 = mysqli_query($con,"INSERT INTO `user` (`username`,`password`,`email`,`fraction`,`activated`,`code`) VALUES ('$username', '".sha1($password)."', '$email', '$frakcia', 1, '$code')") or die (mysqli_error());
//$ins6 = mysqli_query($con,"INSERT INTO `ranking` (`number`) VALUES (0)") or die (mysqli_error());
$query = "INSERT INTO `ranking` (`number`) VALUES (0)";
$ins6  = sql($query);
$query = "
INSERT INTO
  `user`
  (
   `username`,
   `password`,
   `email`,
   `fraction`,
   `activated`,
   `code`
  )
VALUES
  (
    '".$form_sql['username']."', 
    '".$form_sql['password']."', 
    '".$form_sql['email']   ."', 
    '".$form_sql['frakcia'] ."',
    1,
    '".$form_sql['code']    ."'
  )";
$ins3 = sql($query);

// --- a ted jeste jednou, spravne pouziti escapovani
// html
$x = '</b>'; $a = "<b>aaa"; $b = "bbb</b>"
echo $a.escapeHTML($x).$b; // aaa i bbb musi byt tucne, znaky < > se zmeni na &lt; a &gt;
echo $a.$x.$b; // spatne, tucne bude jen aaa

// url
$url = 'https://neco.cz/a='; 
$value = "hve zdi cka";
echo $url.$value;
echo $url.escapeUrl($value); // mezery a divne znaky by to melo nahradit jako %20 a pod

// html url
$a = '<a href="'; $b = '">odkaz</a>';
$url = 'https://neco.cz/a='; 
$value = "hve zdi cka";
echo $a.$url.$value.$b; // chyba, chyba, uplne spatne
echo $a.escapeHtml($url.escapeUrl($value)).$b; // adresu musis escapovat, aby byl platny odkaz a navic je to hodota pro html a ta musi byt escapovana podle html

// sql
$username = 'yy""\'\'xx';
$query = "INSERT INTO `user` (`username`) VALUES ('".escapeSqlValue($username)."')";
// kdyz mas v promenne treba uvozovky, tak bys narusil php, vypadalo by to takto
// "INSERT INTO `user` (`username`) VALUES ('".$username."')"
// "INSERT INTO `user` (`username`) VALUES ('yy""''xx')" // coz je totez jako
// "INSERT INTO `user` (`username`) VALUES ('yy";

// a ted, proc se s tim escapovanim tak pitvam? Protoze to mas spatne!
    $password = mysqli_real_escape_string($con, $_POST['password']);
    $password = htmlspecialchars( $password );
// kdybych si do $password chtel dat uvozovku ' " nebo znak vetsi, mensi > 
// <, tak tvuj kod to naprosto zbytecne prekoduje. Samozrejme to bude 
// fungovat, ale principialne to neni ok. A na zaver z toho delas jeste sha1.
// 1. Kdybys pak chtel heslo zmenit rucne, sql phpmyadmin nabizi moznost 
//    vlozit sha1, takze napises treba string ja>ni<cka, ale pres program se 
//    nenalogujes, protoze tvuj program bude potrebovat ja&gt;ni&lt;cka
// 2. Pokud to heslo vypisujes zpetne do formulare, tak zase nebudou spravne 
//    fungovat uvozovky. ja"ni"cka se ti prepise na ja\"ni\"cka,protoze misto
//    htmlspecialchars(ja"ni"cka) jsi pouzil
//    mysqli_real_escape_string(htmlspecialchars(ja"ni"cka))
// Mozna te to vubec netrapi, ale kdyz tam budes davat treba clanek, ktery 
// ma uvozovky a pouziva vetsitka, mensitka, tak uz to problem je

#2 peter
hmm, nejak postradam pouziti prepared statement, coz byl puvodni tazateluv pozadavek :)

Pouziti prepared statementu ma smysl zaroven s bindovanim parametru a to zas resi escapovani ktere

• v pripade ze je zapnuta emulace prepared statement(default) - si mysqli provede samo
• v pripade ze je emulace prepared statement vypnuta
  Tak escapovani neni potreba protoze se data poslou do databáze mimo vlastní text SQL povelu.
  Je ale dobre bindovani dodrzet vsude, i u cteni. A dat pozor na pripadne injektovani druheho radu.

(pro uplnost, mysqli muze v nekterych pripadech fallbackovat na emulaci prepared statementu i kdyz ten je jako takovy zakazan :( )

Pouzivat jakekoliv jine escapovani (html atp) pro vkladani pres SQL text statement je podle mne dost risk, cimz nerikam ze escapovat html se nema,ale ze se ma delat az tam kde se ma  pouzit.

Priklad prepared statementu je zde (objektovy i proceduralni styl)

http://php.net/manual/en/mysqli.prepare.php

Ovrscout

prepared statement, bind - Vim, nepouzivam, rady necham odbonikum.
Kazdopadne dik za vysvetleni. Kdyz jsem pacil s Kita, proc neni potreba escapovani, tak mi to nedokazal vysvetlit. Ze si to uklada data zvlast a posila pak sql enginu, at si s tim dela, co chce. Ten pak nemusi dekodovat escapovani, zbytecne. Nj, logicke :)

"jine escapovani (html atp) pro vkladani pres SQL text"
Jo, to jsem se pokousel rici. Hlavni riziko je, ze to pridava znaky navic. Pokud mas omezeni policka 100 znaku v html, 100 znaku v sql, tak po escapovani na html tam pribyde treba 30 znaku (100+30) a do db se ulozi jen 100. Takze prijdes o cast textu. Viz to > <, pak se treba pro javascript musi escapovat taky uvozovky &quote; (nevim, zda to htmlentities dela nebo se to musi extra nekde povolit)

funkce("retezec");
funkce('retezec');
funkce('re\'te\"zec');
<span onlick ="funkce(&quote;retezec&quote;);">
<span onlick ="funkce('retezec');">
<span onlick ="funkce('re\'te&quote;zec');">