Jump to content

sql-lover

Members
  • Posts

    37
  • Joined

  • Last visited

    Never

Profile Information

  • Gender
    Not Telling

sql-lover's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. Thanks for reply! In terms of query result of actual display, this is what I want -Show if they are different or not (top of the page) -Get all columns and display table A -Get all the columns and display table B This is how is right now ... -Get all columns and display table A -Get all the columns and display table B -Show if they are different or not (bottom of the page) My problem is, if I tried to show if they are different or not, it won't work, because that loop is using the variable I put to inside each table's loop.
  2. Thanks. Since I posted this question I read additional material. Your comment makes sense.
  3. [quote author=scootstah Not for SQL injection. Why, can you show (with my own code, of course)? not challenging your comment, but I want to fully understand your explanation.
  4. Dummy question here, so please apologize my ignorance ... How many times I can invoke or call a query inside a PHP code if I'm using sqlsrv_fetch_array function? Here's the code (which works like a charm) <!DOCTYPE html> <html> <head> <title>Table Definition's Tool</title> <style type="text/css"> body { font-family: Arial, Verdana, sans-serif; color: #111111;} table { width: 600px;} th, td { padding: 7px 10px 10px 10px;} th { text-transform: uppercase; letter-spacing: 0.1em; font-size: 90%; border-bottom: 2px solid #111111; border-top: 1px solid #999; text-align: left;} tr.even { background-color: #efefef;} tr:hover { background-color: #c3e6e5;} .money { text-align: right;} </style> </head> <body> <?php //Variables $QueryResults1 = Array(); $QueryResults2 = Array(); $dbName=$_POST['dbName']; //Variable for the database name $PROinstance=$_POST['PRO']; //Variable for PRO SQL connection string $ITGinstance=$_POST['ITG']; //Variable for ITG SQL connection string $table=$_POST['table']; //Variable for the table $params = array($table); //Parameters for the T-SQL query, the table name here require 'ErrorHandling.php'; require 'ConnectionInfo.php'; ############################# ##Modify Date for PRO table## ############################# $mdateQuery="SELECT cast( cast (create_date as date) as date), cast( cast (modify_date as date) as date) FROM sys.tables WHERE name=?"; //The proper way is SELECT create_date, dateadd(dd,0, datediff(dd,0, modify_date)) FROM sys.tables WHERE name=?; $TableSchemaQuery = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, NUMERIC_PRECISION, NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=?"; $mdatePROtable = sqlsrv_query($dbPRO, $mdateQuery, $params); if ($mdatePROtable === false) { exitWithSQLError('Wrong table name or table does not exist.'); } $mdateITGtable = sqlsrv_query($dbITG, $mdateQuery, $params); if ($mdateITGtable === false) { exitWithSQLError('Wrong table name or table does not exist.'); } ########################## ##Table Input Validation## ########################## //Main query to validate $validatingQuery ="SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =?"; $validatingPROtable = sqlsrv_query($dbPRO, $validatingQuery, $params); if ($validatingPROtable === false) { exitWithSQLError('Wrong table name or table does not exist.'); } $validatingITGtable = sqlsrv_query($dbITG, $validatingQuery, $params); if ($validatingITGtable === false) { exitWithSQLError('Wrong table name or table does not exist.'); } while ($row1 = sqlsrv_fetch_array($validatingPROtable,SQLSRV_FETCH_NUMERIC)) { $PROtableName=$row1[0]; } while ($row1 = sqlsrv_fetch_array($validatingITGtable,SQLSRV_FETCH_NUMERIC)) { $ITGtableName=$row1[0]; } if ( empty($PROtableName) && empty($ITGtableName) ) { echo "There is no table on PRO or ITG with that name. Please validate with your POR dba."; } elseif (empty($PROtableName)) { echo "There is a table on ITG with that name but not on PRO. Please validate with your POR dba."; } elseif (empty($ITGtableName)){ echo "There is a table on PRO with that name but not on ITG. Please validate with your POR dba."; } //If table's name exists on both, do schema comparison inside following "else" statement else { /* Set up the query for PRO's table. */ // Run PRO query $qresult1 = sqlsrv_query($dbPRO, $TableSchemaQuery, $params); if ($qresult1 === false) { exitWithSQLError('Retrieving schema failed.'); } // Retrieve individual rows from PRO result echo "<h3>Table's structure for \"$table\" - PRO environment</h3>"; echo '<table>'; echo '<thead>'; echo '<tr><th>NAME</th><th>TYPE</th><th>POSITION</th><th>DEFAULT</th><th>LENGHT</th><th>IS NULLABLE</th><th>NUMERIC PRECISION</th><th>NUMERIC SCALE</th></tr>'; echo '</thead>'; echo '<tbody>'; $i=1; while ($row1 = sqlsrv_fetch_array($qresult1,SQLSRV_FETCH_NUMERIC)) { $i++; $QueryResults1[$row1['0']] = $row1; $QueryResults1[$row1['1']] = $row1; $QueryResults1[$row1['2']] = $row1; $QueryResults1[$row1['3']] = $row1; $QueryResults1[$row1['4']] = $row1; $QueryResults1[$row1['5']] = $row1; $QueryResults1[$row1['6']] = $row1; $QueryResults1[$row1['7']] = $row1; if ($i % 2) { echo '<tr class="even">'; echo '<td>' . htmlspecialchars($row1['0']) . '</td>'; echo '<td>' . htmlspecialchars($row1['1']) . '</td>'; echo '<td>' . htmlspecialchars($row1['2']) . '</td>'; echo '<td>' . htmlspecialchars($row1['3']) . '</td>'; echo '<td>' . htmlspecialchars($row1['4']) . '</td>'; echo '<td>' . htmlspecialchars($row1['5']) . '</td>'; echo '<td>' . htmlspecialchars($row1['6']) . '</td>'; echo '<td>' . htmlspecialchars($row1['7']) . '</td>'; echo '</tr>'; } else { echo '<tr>'; echo '<td>' . htmlspecialchars($row1['0']) . '</td>'; echo '<td>' . htmlspecialchars($row1['1']) . '</td>'; echo '<td>' . htmlspecialchars($row1['2']) . '</td>'; echo '<td>' . htmlspecialchars($row1['3']) . '</td>'; echo '<td>' . htmlspecialchars($row1['4']) . '</td>'; echo '<td>' . htmlspecialchars($row1['5']) . '</td>'; echo '<td>' . htmlspecialchars($row1['6']) . '</td>'; echo '<td>' . htmlspecialchars($row1['7']) . '</td>'; echo '</tr>'; } } echo '</tbody>'; echo '</table>'; echo '<br>'; // null == no further rows, false == error if ($row1 === false) { exitWithSQLError('Retrieving schema failed.'); } while ( $row1 = sqlsrv_fetch( $mdatePROtable)) { echo "<p>Created on " .sqlsrv_get_field( $mdatePROtable, 0, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR))." and last time modified on ".sqlsrv_get_field( $mdatePROtable, 1, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR))."</p>"; } /* Set up the query for ITG's table. */ $qresult2 = sqlsrv_query($dbITG, $TableSchemaQuery, $params); if ($qresult2 === false) { exitWithSQLError('Query of product data failed.'); } // Retrieve individual rows from ITG result echo "<h3>Table's structure for \"$table\" - ITG environment</h3>"; echo '<table>'; echo '<thead>'; echo '<tr><th>NAME</th><th>TYPE</th><th>POSITION</th><th>DEFAULT</th><th>LENGHT</th><th>IS NULLABLE</th><th>NUMERIC PRECISION</th><th>NUMERIC SCALE</th></tr>'; echo '</thead>'; echo '<tbody>'; $i=1; while ($row2 = sqlsrv_fetch_array($qresult2,SQLSRV_FETCH_NUMERIC)) { $i++; $QueryResults2[$row2['0']] = $row2; $QueryResults2[$row2['1']] = $row2; $QueryResults2[$row2['2']] = $row2; $QueryResults2[$row2['3']] = $row2; $QueryResults2[$row2['4']] = $row2; $QueryResults2[$row2['5']] = $row2; $QueryResults2[$row2['6']] = $row2; $QueryResults2[$row2['7']] = $row2; if ($i % 2) { echo '<tr class="even">'; echo '<td>' . htmlspecialchars($row2['0']) . '</td>'; echo '<td>' . htmlspecialchars($row2['1']) . '</td>'; echo '<td>' . htmlspecialchars($row2['2']) . '</td>'; echo '<td>' . htmlspecialchars($row2['3']) . '</td>'; echo '<td>' . htmlspecialchars($row2['4']) . '</td>'; echo '<td>' . htmlspecialchars($row2['5']) . '</td>'; echo '<td>' . htmlspecialchars($row2['6']) . '</td>'; echo '<td>' . htmlspecialchars($row2['7']) . '</td>'; echo '</tr>'; } else { echo '<tr>'; echo '<td>' . htmlspecialchars($row2['0']) . '</td>'; echo '<td>' . htmlspecialchars($row2['1']) . '</td>'; echo '<td>' . htmlspecialchars($row2['2']) . '</td>'; echo '<td>' . htmlspecialchars($row2['3']) . '</td>'; echo '<td>' . htmlspecialchars($row2['4']) . '</td>'; echo '<td>' . htmlspecialchars($row2['5']) . '</td>'; echo '<td>' . htmlspecialchars($row2['6']) . '</td>'; echo '<td>' . htmlspecialchars($row2['7']) . '</td>'; echo '</tr>'; } } echo '</tbody>'; echo '</table>'; echo '<br>'; while ( $row1 = sqlsrv_fetch( $mdateITGtable)) { echo "<p>Created on " .sqlsrv_get_field( $mdateITGtable, 0, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR))." and last time modified on ".sqlsrv_get_field( $mdateITGtable, 1, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR))."</p>"; } // null == no further rows, false == error if ($row2 === false) { exitWithSQLError('Retrieving schema failed.'); } //Comparing Arrays foreach ($QueryResults1 as $key => $value) { if ($QueryResults2[$key]!=$value) { echo '<p><span style="BACKGROUND-COLOR: #ffff00">The schemas are different!</span></p>'; break; } } $PROTableSize=sizeof($QueryResults1); $ITGTableSize=sizeof($QueryResults2); if ($PROTableSize==$ITGTableSize){ echo "<p>Schemas are identical!</p>\n"; } // Releasing resources and closing connections sqlsrv_free_stmt($qresult1); sqlsrv_free_stmt($qresult2); } // Releasing resources and closing connections sqlsrv_free_stmt($validatingITGtable); sqlsrv_free_stmt($validatingPROtable); sqlsrv_close($dbPRO); sqlsrv_close($dbITG); ?> </body> </html> Not the most elegante code, I'm still learning PHP, but this is what it does: - [*]Use a T-SQL query to get the table's schema of a given table [*]Run same query on two different MS-SQL instances [*]Display result for each one [*]Compare results to see if they match The problem I have (logic, not syntax) is that I want the comparison's result to be display 1st or way on the top. But if I do that, won't work, as I am capturing the result of each query or array on some variables, $QueryResults1 and $QueryResults2. If I try to run the while plus sqlsrv_fetch_array twice (1st to capture the results, 2nd to actually display) it gives me an error or does not display anything. How many times can I call sqlsrv_fetch_array inside a PHP code? Or, how can I compare both results so the echo statement can appear way on top? Hope I explain it correctly, thanks in advance
  5. They are essentially the same. Your just passing the parameters and sql together in a call vs doing it separately by preparing the query first then binding parameters later. I guess that my question is still there :-) Do I have to sanitize the input? And if answer is Yes, which function or function will help me now than I'm using the Microsoft connector or driver.
  6. "blonde moment" You're right. Prepared isn't the same as parameterized. But I would prefer not to redesign the whole code and change to prepared if using a parameter is still safe.
  7. Thanks for reply. isn't prepare statement the same as parametrized queries? but anyway, my question is about above code. Do I have to sanitize after using a parameterized query?
  8. Hi, I am using parameterized queries on my code, here's the relevant part $params=$_POST['ITGtable']; $tsql2 = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=?"; /* Execute the statement with the specified parameter value. Display the returned data if no errors occur. */ $stmt2 = sqlsrv_query( $conn, $tsql2, $params); if( $stmt2 === false ) { echo "Statement 2 could not be executed.\n"; die( print_r(sqlsrv_errors(), true)); } else { $qty = sqlsrv_fetch_array( $stmt2); } Do I really have to sanitize $_POST['ITGtable'] for apostrophe, semicolon, etc, to avoid SQL injection problems? Or just with above code I should be safer (I did not say safe) against SQL injection? And if the answer is "No", what could be the sanitize code of function? I am using sqlsrv and MS-SQL database engine; most of the functions we have for sanitize inputs on MySQL are not available for MS-SQL. Thanks in advance,
  9. 1st, Thanks for the MOD which moved it to the right forum :-) Now, to dmikester1, appreciate your response. My CSS knowledge is very limited, but I used to code pure HTML back in 1994 (yeah, maybe I'm older than you, lol) so I can catch up concepts quickly. I'm actually a professional DBA. What I am trying to accomplish is make a clear distinction between the headers and the content. Also, I would like to experiment with some type of grid, like creating a table? So results can be easier to read. Not so sure how it will look like, that's why I want to play a little bit. But I don't know how to generate such grid. However, you gave me a basic idea of how change color for text and headers.
  10. Hi, Need some advises or quick help with my query result layout (attached to this post) I'm currently using this code to organize and show my query's result echo '<tr><th>NAME</th><th>TYPE</th><th>POSITION</th><th>DEFAULT</th><th>LENGHT</th><th>IS NULLABLE</th><th>NUMERIC PRECISION</th><th>NUMERIC SCALE</th></tr>'; // Retrieve individual rows from the result while ($row1 = sqlsrv_fetch_array($qresult1,SQLSRV_FETCH_NUMERIC)) { $QueryResults1[$row1['0']] = $row1; $QueryResults1[$row1['1']] = $row1; $QueryResults1[$row1['2']] = $row1; $QueryResults1[$row1['3']] = $row1; $QueryResults1[$row1['4']] = $row1; $QueryResults1[$row1['5']] = $row1; $QueryResults1[$row1['6']] = $row1; $QueryResults1[$row1['7']] = $row1; echo '<tr><td>', htmlspecialchars($row1['0']), '</td><td>', htmlspecialchars($row1['1']), '</td><td>', htmlspecialchars($row1['2']), '</td><td>', htmlspecialchars($row1['3']), '</td><td>', htmlspecialchars($row1['4']), '</td><td>', htmlspecialchars($row1['5']), '</td><td>', htmlspecialchars($row1['6']), '</td><td>', htmlspecialchars($row1['7']), "</td></tr>\n"; } and echo '<tr><th>NAME</th><th>TYPE</th><th>POSITION</th><th>DEFAULT</th><th>LENGHT</th><th>IS NULLABLE</th><th>NUMERIC PRECISION</th><th>NUMERIC SCALE</th></tr>'; // Retrieve individual rows from the result while ($row2 = sqlsrv_fetch_array($qresult2,SQLSRV_FETCH_NUMERIC)) { $QueryResults2[$row2['0']] = $row2; $QueryResults2[$row2['1']] = $row2; $QueryResults2[$row2['2']] = $row2; $QueryResults2[$row2['3']] = $row2; $QueryResults2[$row2['4']] = $row2; $QueryResults2[$row2['5']] = $row2; $QueryResults2[$row2['6']] = $row2; $QueryResults2[$row2['7']] = $row2; echo '<tr><td>', htmlspecialchars($row2['0']), '</td><td>', htmlspecialchars($row2['1']), '</td><td>', htmlspecialchars($row2['2']), '</td><td>', htmlspecialchars($row2['3']), '</td><td>', htmlspecialchars($row2['4']), '</td><td>', htmlspecialchars($row2['5']), '</td><td>', htmlspecialchars($row2['6']), '</td><td>', htmlspecialchars($row2['7']), "</td></tr>\n"; } Of course, that's the relevant part of the code. The query and connection not included. I would like to maybe use css, better fonts or even color. It actually looks boring and ugly. What markup code should I use or how can I modify that part of the code so I can get a more professional or easy to read display?
  11. Hi, While trying to create my own script that allows you to compare two different files , found this amazing algorithm from Paul Butler function diff($oldhistory, $newhistory){ foreach($oldhistory as $oindex => $ovalue){ $nkeys = array_keys($newhistory, $ovalue); foreach($nkeys as $nindex){ $matrix[$oindex][$nindex] = isset($matrix[$oindex - 1][$nindex - 1]) ? $matrix[$oindex - 1][$nindex - 1] + 1 : 1; if($matrix[$oindex][$nindex] > $maxlen){ $maxlen = $matrix[$oindex][$nindex]; $omax = $oindex + 1 - $maxlen; $nmax = $nindex + 1 - $maxlen; } } } if($maxlen == 0) return array(array('d'=>$oldhistory, 'i'=>$newhistory)); return array_merge( diff(array_slice($oldhistory, 0, $omax), array_slice($newhistory, 0, $nmax)), array_slice($newhistory, $nmax, $maxlen), diff(array_slice($oldhistory, $omax + $maxlen), array_slice($newhistory, $nmax + $maxlen))); } function htmlDiff($oldhistory, $newhistory){ $diff = diff(explode(' ', $oldhistory), explode(' ', $newhistory)); foreach($diff as $k){ if(is_array($k)) $ret .= (!empty($k['d'])?"<del>".implode(' ',$k['d'])."</del> ":''). (!empty($k['i'])?"<ins>".implode(' ',$k['i'])."</ins> ":''); else $ret .= $k . ' '; } return $ret; } This is embarrassing, but how can I test this with two text variables? Where should I wrote the echo line to display some output? I'm new to PHP. Also, my own and extremely simplify idea is using strtoupper and md5 for comparing two texts. Is that a good logic? The main goal of my program is able to compare two MS-SQL store procedures from two different servers and tell if they both are the same of different versions. Thanks in advance,
  12. Ohh, thats not a problem :-) ... I can post the whole code again. I'm right now focus on the input validation and security part.
  13. Thanks, fixed .... this way ... $params = array($table); Very silly mistake, but thanks again for pointing that out!
  14. Trying to make my code more secure. This is what I currently have, which is not secure by any means: $query1 = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='$table'"; // Run PRO query $qresult1 = sqlsrv_query($dbPRO, $query1); if ($qresult1 === false) { exitWithSQLError('Retrieving schema failed.'); } This is how I changed it, $query1 = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=?"; $params = array(1, $table); // Run PRO query $qresult1 = sqlsrv_query($dbPRO, $query1, $params); if ($qresult1 === false) { exitWithSQLError('Retrieving schema failed.'); } but I'm getting this error: SQL-Status: 22018 Code: 245 Message: [Microsoft][sql Server Native Client 10.0][sql Server]Conversion failed when converting the nvarchar value 'sysrscols' to data type int Please notice I am using sqlsrv_query function because my database engine is MS-SQL 2008. That's why I'm a bit confused. Most documentation online is pointed to MySQL. exitWithSQLError is a customized function of mine, so please ignore. Any help or hints is appreciated, Thanks,
×
×
  • 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.